FTS Index Size: hierarchy

显示标签为“hierarchy”的博文。显示所有博文

2012年3月27日星期二

Flattening Parent Child, an issue, please help

Hello Experts,
Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

How do modify the code to work for any level rather than hard coding
the level up to "5"?
Please help.
Thanks.
SoumyaDip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
How do modify the code to work for any level rather than hard coding
the level up to "5"?

If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.

If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

How do modify the code to work for any level rather than hard coding
the level up to "5"?

>
If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.
>
If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||>Here is the code to flatten a PC hierarchy into a level based table. <<

I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.

Quote:

Originally Posted by

Quote:

Originally Posted by

>How do modify the code to work for any level rather than hard coding the level up to "5"? <<

One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.

A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.

The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.

You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.|||Hi Celko,
Thanks for your input.
The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!
Any help is greatly appriciated.
Thanks.
Soumya

--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. <<

>
I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

How do modify the code to work for any level rather than hard coding the level up to "5"? <<

>
One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.
>
A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.
>
The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.
>
You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!

You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?
Regards,
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

>
You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?

Did you even look at the article I posted the link to?

What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.

It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)

I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.

What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,
I actually printed out your article and went through it. It is actually
very well written and covers all general situations, however, I
didn't have much luck constructing the Dynamic SQL to generate
"possible" columns and add each "LEFT OUTER JOIN" for each
level. Even if I break it down to two SQL Text, I would still need to
tell it to add 10 columns for each level for example and 9 LEFT OUTER
JOINs to break the Parent Child Adjacent model if WBS_LEVEL is 10 for
instance.

I have designed Stored Proc with Dynamic SQL in it but I haven't done
anything like this one before. Either it is silly simple or I just
can't get my head around to it.

I don't think any literature would help me to solve this problem but
some actual code that relates to this issue.
Thanks for all help.
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

>
Did you even look at the article I posted the link to?
>
What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.
>
It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)
>
I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.
>
What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Flattening Parent Child Hierarchy: Urgent please help

Hi Expert,
How do I flatten a Parent Child hierarchy to regular flat data: please
provide some SQL code:

I have now:
Task_ID, Parent_Task_ID, Task_NameLevel
11Project Management1
21Costing2
31Estimating2
42Task13
52Task23
63Task33
73Task43

I want to have:

Level1Level2Level3
Project ManagementCostingTask1
Project ManagementCostingTask2
Project ManagementEstimatingTask3
Project ManagementEstimatingTask4

Please help, I am stuck!
Thanks in advance.
SoumyaDip wrote:

Quote:

Originally Posted by

Hi Expert,
How do I flatten a Parent Child hierarchy to regular flat data: please
provide some SQL code:
>
I want to have:
>
Level1Level2Level3
Project ManagementCostingTask1
Project ManagementCostingTask2
Project ManagementEstimatingTask3
Project ManagementEstimatingTask4
>

Sounds pretty straightforward, joining the table into itself as many
times as you need to get the depth you want. What have you tried so
far, and what is the specific issue you're coming up against?

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/|||On 10 Aug 2006 07:16:56 -0700, "Jason Kester" <jasonkester@.gmail.com>
wrote:

Quote:

Originally Posted by

>Dip wrote:

Quote:

Originally Posted by

>Hi Expert,
>How do I flatten a Parent Child hierarchy to regular flat data: please
>provide some SQL code:
>>
>I want to have:
>>
>Level1Level2Level3
>Project ManagementCostingTask1
>Project ManagementCostingTask2
>Project ManagementEstimatingTask3
>Project ManagementEstimatingTask4
>>

>
>
>Sounds pretty straightforward, joining the table into itself as many
>times as you need to get the depth you want. What have you tried so
>far, and what is the specific issue you're coming up against?

That is:

select
mt1.Task_Name Level1,
mt2.Task_Name Level2,
mt3.Task_Name Level3
from my_table mt1
join my_table mt2 on mt2.Parent_Task_ID = mt1.Task_ID
and mt2.Level = 2
join my_table mt3 on mt3.Parent_Task_ID = mt2.Task_ID
and mt3.Level = 3

If you're not guaranteed to have data at all levels, then replace the
joins with left joins.

If you don't trust Level to be accurate, but do trust all and only
first-level rows to have Parent_Task_ID = their own Task_ID, then
do this instead:

select
mt1.Task_Name Level1,
mt2.Task_Name Level2,
mt3.Task_Name Level3
from my_table mt1
join my_table mt2 on mt2.Parent_Task_ID = mt1.Task_ID
and mt1.Parent_Task_ID = mt1.Task_ID
and mt2.Parent_Task_ID <mt2.Task_ID
join my_table mt3 on mt3.Parent_Task_ID = mt2.Task_ID

Flatten N-Tier Hierarchy for reporting

Hello. I have a specification for a new application that states that a
particular item structure should support a series of arbitrary hierarchies.
Hierarchy types would be defined in a lookup table, such that items and chil
d
items would be of a particular "type," but this is really only for UI displa
y
purposes -- all items will stored in the same table in the database. Also,
the lowest level of a given heirarchy type should also support cost
accumulation. My hierarchy setup is a standard Id/ParentId model, and I
created an ItemDetail table (FK on the ItemID) to store the cost records.
I have built a c# prototype application that supports this structure, and
everything works like a champ. Here's the problem I have and question on
which I need input: I don't know how to report on it. Since the hierarchy
types have an arbitrary number of levels, how, thru TSQL, do I create a view
on which users can create reports? Through code (c#), I can recurse the
Items table and compare against the ItemTypes table to inspect the hierarchy
types and create tabular (flattened) data. My goal is to expose a view (or
set of views) for end users to use for ad-hoc reporting.
Below is a simplified/truncated data structure similar to my prototype
structure, and my desired end-result. Since I'm still in a prototype stage,
I'm not stuck to the data model, so I'm open to suggestions for improvement
to the design or suggestions on the reporting issue. I hope all this makes
sense, and thanks in advance.
Note: All the primary keys are simple identity columns, because the form of
the data that user knows as the key will vary.
CREATE TABLE [GroupTypes] (
[grptypPk] [int] IDENTITY (1, 1) NOT NULL ,
[grptypName] [varchar] (50) NOT NULL ,
)
CREATE TABLE [Groups] (
[grpPk] [int] IDENTITY (1, 1) NOT NULL ,
[grpName] [varchar] (50) NOT NULL ,
[grpGroupType_fk] [int] NOT NULL
)
CREATE TABLE [ItemTypes] (
[itmtypPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmtypName] [varchar] (50) NOT NULL ,
[itmtypParent] [int] NULL ,
[itmtypGroupType_fk] [int] NOT NULL
)
CREATE TABLE [Items] (
[itmPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmName] [varchar] (50) NOT NULL ,
[itmItemType_fk] [int] NOT NULL ,
[itmGroup_fk] [int] NOT NULL ,
[itmParent] [int] NULL
)
CREATE TABLE [ItemDetailTypes] (
[dtltypPk] [int] IDENTITY (1, 1) NOT NULL ,
[dtltypName] [varchar] (50) NOT NULL ,
)
CREATE TABLE [ItemDetail] (
[itmdtlPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmdtlCost] [int] NOT NULL ,
[itmdtlItem_fk] [int] NOT NULL ,
[itmdtlDetailType_fk] [int] NOT NULL
)
/*----*/
DECLARE @.itmtyp2 INT
DECLARE @.itmtyp3 INT
DECLARE @.itm3 INT
DECLARE @.itm6 INT
DECLARE @.dtltyp1 INT
INSERT INTO GroupTypes (grptypName) VALUES ('Group Type 1')
SET @.grptyp1 = @.@.IDENTITY
INSERT INTO Groups (grpName, grpGroupType_fk) VALUES ('Group1', @.grptyp1)
SET @.grp1 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 1', NULL, @.grptyp1)
SET @.itmtyp1 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 2', @.itmtyp1, @.grptyp1)
SET @.itmtyp2 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 3', @.itmtyp2, @.grptyp1)
SET @.itmtyp3 = @.@.IDENTITY
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item1', @.itmtyp1, @.grp1, NULL)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item2', @.itmtyp2, @.grp1, @.@.IDENTITY)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item3', @.itmtyp3, @.grp1, @.@.IDENTITY)
SET @.itm3 = @.@.IDENTITY
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item4', @.itmtyp1, @.grp1, NULL)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item5', @.itmtyp2, @.grp1, @.@.IDENTITY)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item6', @.itmtyp3, @.grp1, @.@.IDENTITY)
SET @.itm6 = @.@.IDENTITY
INSERT INTO ItemDetailTypes (dtltypName) VALUES ('Detail Type 1')
SET @.dtltyp1 = @.@.IDENTITY
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(10, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(20, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(30, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(15, @.itm6, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(25, @.itm6, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(30, @.itm6, @.dtltyp1)
/*----*/
Flattened data for Group1:
Group1 Item1 Item2 Item3
Group1 Item4 Item5 Item6
I believe this is an easy structure on which to report, and I could just
INNER JOIN on the ItemDetail table for Cost information.Hi
The best way to do this is to traverse the hierachy and build up the rows on
the the client.
There are many posts regarding hierarchies in SQL Server, so you may also
want to search Google for previous posts.
John
"Steve" wrote:

> Hello. I have a specification for a new application that states that a
> particular item structure should support a series of arbitrary hierarchies
.
> Hierarchy types would be defined in a lookup table, such that items and ch
ild
> items would be of a particular "type," but this is really only for UI disp
lay
> purposes -- all items will stored in the same table in the database. Also
,
> the lowest level of a given heirarchy type should also support cost
> accumulation. My hierarchy setup is a standard Id/ParentId model, and I
> created an ItemDetail table (FK on the ItemID) to store the cost records.
> I have built a c# prototype application that supports this structure, and
> everything works like a champ. Here's the problem I have and question on
> which I need input: I don't know how to report on it. Since the hierarchy
> types have an arbitrary number of levels, how, thru TSQL, do I create a vi
ew
> on which users can create reports? Through code (c#), I can recurse the
> Items table and compare against the ItemTypes table to inspect the hierarc
hy
> types and create tabular (flattened) data. My goal is to expose a view (o
r
> set of views) for end users to use for ad-hoc reporting.
> Below is a simplified/truncated data structure similar to my prototype
> structure, and my desired end-result. Since I'm still in a prototype stag
e,
> I'm not stuck to the data model, so I'm open to suggestions for improvemen
t
> to the design or suggestions on the reporting issue. I hope all this make
s
> sense, and thanks in advance.
>
> Note: All the primary keys are simple identity columns, because the form o
f
> the data that user knows as the key will vary.
> CREATE TABLE [GroupTypes] (
> [grptypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [grptypName] [varchar] (50) NOT NULL ,
> )
> CREATE TABLE [Groups] (
> [grpPk] [int] IDENTITY (1, 1) NOT NULL ,
> [grpName] [varchar] (50) NOT NULL ,
> [grpGroupType_fk] [int] NOT NULL
> )
> CREATE TABLE [ItemTypes] (
> [itmtypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmtypName] [varchar] (50) NOT NULL ,
> [itmtypParent] [int] NULL ,
> [itmtypGroupType_fk] [int] NOT NULL
> )
> CREATE TABLE [Items] (
> [itmPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmName] [varchar] (50) NOT NULL ,
> [itmItemType_fk] [int] NOT NULL ,
> [itmGroup_fk] [int] NOT NULL ,
> [itmParent] [int] NULL
> )
> CREATE TABLE [ItemDetailTypes] (
> [dtltypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [dtltypName] [varchar] (50) NOT NULL ,
> )
> CREATE TABLE [ItemDetail] (
> [itmdtlPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmdtlCost] [int] NOT NULL ,
> [itmdtlItem_fk] [int] NOT NULL ,
> [itmdtlDetailType_fk] [int] NOT NULL
> )
>
> /*----*
/
>
> DECLARE @.itmtyp2 INT
> DECLARE @.itmtyp3 INT
> DECLARE @.itm3 INT
> DECLARE @.itm6 INT
> DECLARE @.dtltyp1 INT
>
> INSERT INTO GroupTypes (grptypName) VALUES ('Group Type 1')
> SET @.grptyp1 = @.@.IDENTITY
> INSERT INTO Groups (grpName, grpGroupType_fk) VALUES ('Group1', @.grptyp1)
> SET @.grp1 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 1', NULL, @.grptyp1)
> SET @.itmtyp1 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 2', @.itmtyp1, @.grptyp1)
> SET @.itmtyp2 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 3', @.itmtyp2, @.grptyp1)
> SET @.itmtyp3 = @.@.IDENTITY
>
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item1', @.itmtyp1, @.grp1, NULL)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item2', @.itmtyp2, @.grp1, @.@.IDENTITY)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item3', @.itmtyp3, @.grp1, @.@.IDENTITY)
> SET @.itm3 = @.@.IDENTITY
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item4', @.itmtyp1, @.grp1, NULL)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item5', @.itmtyp2, @.grp1, @.@.IDENTITY)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item6', @.itmtyp3, @.grp1, @.@.IDENTITY)
> SET @.itm6 = @.@.IDENTITY
> INSERT INTO ItemDetailTypes (dtltypName) VALUES ('Detail Type 1')
> SET @.dtltyp1 = @.@.IDENTITY
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (10, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (20, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (30, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (15, @.itm6, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (25, @.itm6, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (30, @.itm6, @.dtltyp1)
> /*----*
/
> Flattened data for Group1:
> Group1 Item1 Item2 Item3
> Group1 Item4 Item5 Item6
> I believe this is an easy structure on which to report, and I could just
> INNER JOIN on the ItemDetail table for Cost information.
>|||Thanks John. I've actually done my due-diligence Googling, but I couldn't
find what I was looking for. My Google results returned _lots_ of results o
n
how to transform a flat dataset into a hierarchical one, but not the reverse
!
I'd like for the users to be able to use generic reporting tools, such as
Access, etc., to be able to report on the data. So I'm looking for a way, i
n
SQL, or more generally, at the database-level, to present the data in a way
that doesn't require special code in order to group/subtotal.
"John Bell" wrote:
> Hi
> The best way to do this is to traverse the hierachy and build up the rows
on
> the the client.
> There are many posts regarding hierarchies in SQL Server, so you may also
> want to search Google for previous posts.
> John
> "Steve" wrote:
>|||Hi
If you return your hierarchy in order then the client can flatten it. This
will be the fastest solution!
For traversing the hierarchy posts like http://tinyurl.com/o3rc are a good
start.
To produce a crosstab output from the above results try something like
http://www.windowsitpro.com/SQLServ...5608/15608.html
John
"Steve" wrote:
> Thanks John. I've actually done my due-diligence Googling, but I couldn't
> find what I was looking for. My Google results returned _lots_ of results
on
> how to transform a flat dataset into a hierarchical one, but not the rever
se!
> I'd like for the users to be able to use generic reporting tools, such as
> Access, etc., to be able to report on the data. So I'm looking for a way,
in
> SQL, or more generally, at the database-level, to present the data in a wa
y
> that doesn't require special code in order to group/subtotal.
>
> "John Bell" wrote:
>

订阅：博文 (Atom)

2012年3月27日星期二

Flattening Parent Child, an issue, please help

Flattening Parent Child Hierarchy: Urgent please help

Flatten N-Tier Hierarchy for reporting

FTS Index Size

Blog Archive

About Me