July 31, 2012 at 4:23 am
CREATE TABLE #tblItems
(
ItemID int,
ItemDescription varchar(50)
)
GO
INSERT INTO #tblItems
(ItemID, ItemDescription)
SELECT 1, 'Item 1' UNION ALL
SELECT 2, 'Item 2' UNION ALL
SELECT 3, 'Item 3' UNION ALL
SELECT 4, 'Item 4' UNION ALL
SELECT 5, 'Item 5' UNION ALL
SELECT 6, 'Item 6' UNION ALL
SELECT 7, 'Item 7' UNION ALL
SELECT 8, 'Item 8' UNION ALL
SELECT 9, 'Item 9' UNION ALL
SELECT 10, 'Item 10'
GO
CREATE TABLE #tblItemRelationship
(
ParentItemID int,
ChildItemID int
)
GO
INSERT INTO #tblItemRelationship
(ParentItemID, ChildItemID)
SELECT 1, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 3, 7 UNION ALL
SELECT 4, 8 UNION ALL
SELECT 7, 9 UNION ALL
SELECT 7, 10
GO
So, tblItems is a list of items and tblItemRelationship shows a parent child relationship between the items.
How do you write a select statement that would do this ... take an ItemID as a ParentID and show its children and show their children in a hierarchy.
So, for example, 'show me the children of Item ID 1' and you'd end up with data that would allow you to show data something like this.
Item 1
--Item 2
----Item 3
------Item 6
------Item 7
--------Item 9
--------Item 10
----Item 4
------Item 8
----Item 5
i,e. Item 1 has Item 2 below it.
Item 2 has Items 3,4 and 5 below it
Item 3 has Items 6 and 7 below it
Item 4 has Item 8 below it
etc.
I've been racking my feeble brain over this for days - my efforts involve endless temporary tables and inserts from one table to another and so on. Is there a simple way to do this?
July 31, 2012 at 4:44 am
Here's a rCTE to get you started:
;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
UNION ALL
SELECT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
)
SELECT *
FROM rCTE
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2012 at 5:10 am
http://www.sqlservercentral.com/Forums/Topic1337063-391-1.aspx
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
July 31, 2012 at 5:13 am
Hi and thanks very much for that.
It produces in the list column:
1>2
1>2>3
1>2>4
1>2>5
1>2>4>8
1>2>3>6
1>2>3>7>9
1>2>3>7>10
What I need it to produce is:
1>2
1>2>3
1>2>3>6
1>2>3>7
1>2>3>7>9
1>2>3>7>10
1>2>4
1>2>4>8
1>2>5
I've tried various sorting but I can't make it do that. And although it's producing:
1>2
1>2>3
1>2>4
1>2>5
it is missing out:
1>2>3>7
July 31, 2012 at 5:27 am
Thanks for the link ... I'm just seeing a blank page.
July 31, 2012 at 5:30 am
demonfox (7/31/2012)
http://www.sqlservercentral.com/Forums/Topic1337063-391-1.aspx
This link doesn't work.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2012 at 5:34 am
Here's the output of the rCTE:
LevelParentItemIDChildItemIDList
1121>2
2231>2>3
2241>2>4
2251>2>5
3481>2>4>8
3361>2>3>6
3371>2>3>7
4791>2>3>7>9
47101>2>3>7>10
sku370870 (7/31/2012)
Hi and thanks very much for that.It produces in the list column:
1>2
1>2>3
1>2>4
1>2>5
1>2>4>8
1>2>3>6
1>2>3>7>9
1>2>3>7>10
What I need it to produce is:
1>2
1>2>3
1>2>3>6
1>2>3>7
1>2>3>7>9
1>2>3>7>10
1>2>4
1>2>4>8
1>2>5
I've tried various sorting but I can't make it do that. And although it's producing:
1>2
1>2>3
1>2>4
1>2>5
it is missing out:
1>2>3>7
Looks ok to me. Try ORDER BY List
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2012 at 6:08 am
Apologies - you're right - bit of brain freeze here I guess.
If I sort by List I get:
1>2
1>2>3
1>2>3>6
1>2>3>7
1>2>3>7>10
1>2>3>7>9
1>2>4
1>2>4>8
1>2>5
So, the only niggle is that 1>2>3>7>10 is appearing before 1>2>3>7>9
I've tried this:
;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
UNION ALL
SELECT TOP 100 PERCENT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
ORDER BY lr.ChildItemID ASC
)
SELECT *
FROM rCTE
ORDER BY List
bit it does not affect the sort order. Thanks again for your help.
July 31, 2012 at 6:17 am
sku370870 (7/31/2012)
Apologies - you're right - bit of brain freeze here I guess.If I sort by List I get:
1>2
1>2>3
1>2>3>6
1>2>3>7
1>2>3>7>10
1>2>3>7>9
1>2>4
1>2>4>8
1>2>5
So, the only niggle is that 1>2>3>7>10 is appearing before 1>2>3>7>9
I've tried this:
;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
UNION ALL
SELECT TOP 100 PERCENT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
ORDER BY lr.ChildItemID ASC
)
SELECT *
FROM rCTE
ORDER BY List
bit it does not affect the sort order. Thanks again for your help.
'1' appears before '9' - it's a string. Putting in a leading zero fixes this:
;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(
RIGHT('0'+CAST(tr.ParentItemID AS VARCHAR(10)),2)
+ '>' +
RIGHT('0'+CAST(tr.ChildItemID AS VARCHAR(10)),2)
AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
UNION ALL
SELECT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' +
RIGHT('0'+CAST(tr.ChildItemID AS VARCHAR(10)),2)
AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
)
SELECT *
FROM rCTE
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2012 at 6:52 am
I'm trying to understand your code ...
In this example your first select statement
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
selects one row from #tblItemRelationship table which matches the parentID we're interested in and aliases this as 'tr'
Your second select ...
SELECT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
effectively joins #tblItemRelationship to itself - to the alias 'tr'
But 'tr' only contains one row - I don't understand how/why the derived(?) table rCTE manages to seem to do multiple joins to itself ... and the syntax - there is a 'with rCTE' statement that seems to enclose all the code, and then rCTE is aliased as 'lr' ... whole thing baffles me and I'd really like to understand. I wonder if you could explain please.
Thanks again.
July 31, 2012 at 7:05 am
One final thing. If the items table contained a date column, how could one force every set of child items to be sorted by date?
CREATE TABLE #tblItems
(
ItemID int,
ItemDescription varchar(50),
ItemDate smalldatetime
)
GO
INSERT INTO #tblItems
(ItemID, ItemDescription, ItemDate)
SELECT 1, 'Item 1', '01 Jan 2012' UNION ALL
SELECT 2, 'Item 2', '03 Jan 2012' UNION ALL
SELECT 3, 'Item 3', '05 Jan 2012' UNION ALL
SELECT 4, 'Item 4', '02 Jan 2012' UNION ALL
SELECT 5, 'Item 5', '07 Jan 2012' UNION ALL
SELECT 6, 'Item 6', '09 Jan 2012' UNION ALL
SELECT 7, 'Item 7', '08 Jan 2012' UNION ALL
SELECT 8, 'Item 8', '08 Jan 2012' UNION ALL
SELECT 9, 'Item 9', '12 Jan 2012' UNION ALL
SELECT 10, 'Item 10', '14 Jan 2012'
GO
which (I think) ought to produce
Item 1
--Item 2
----Item 4
------Item 8
----Item 3
------Item 7
--------Item 9
--------Item 10
------Item 6
----Item 5
i.e. that Items 3,4 and 5 are still children of Item 2 but Item 4 occurs before item 3 so the sort needs to be on date.
And Items 6 and 7 are children of Item 3 but 7 appears before 6 based on the date.
Thanks once more.
July 31, 2012 at 7:12 am
Craig Freedman has an excellent description of how recursive CTE's work.
What's important to remember about rCTE's is that only the result of the last recursive iteration (the alias lr) is visible to join in the current recursive iteration (the alias tr).
Aliases lr (last row) and tr (this row) are more descriptive when a rCTE is used to scan through a sequenced column in a table but work well enough here to represent the recursive level.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2012 at 7:15 am
Thanks for the link - I'm beginning to comprehend.
July 31, 2012 at 11:45 am
Decided to try this myself since I haven't used recursive CTE's. It was a good exercise.
CREATE TABLE #tblItems
(
ItemID int,
ItemDescription varchar(50),
ItemDate smalldatetime
);
GO
INSERT INTO #tblItems
(ItemID, ItemDescription, ItemDate)
SELECT 1, 'Item 1', '01 Jan 2012' UNION ALL
SELECT 2, 'Item 2', '03 Jan 2012' UNION ALL
SELECT 3, 'Item 3', '05 Jan 2012' UNION ALL
SELECT 4, 'Item 4', '02 Jan 2012' UNION ALL
SELECT 5, 'Item 5', '07 Jan 2012' UNION ALL
SELECT 6, 'Item 6', '09 Jan 2012' UNION ALL
SELECT 7, 'Item 7', '08 Jan 2012' UNION ALL
SELECT 8, 'Item 8', '08 Jan 2012' UNION ALL
SELECT 9, 'Item 9', '12 Jan 2012' UNION ALL
SELECT 10, 'Item 10', '14 Jan 2012';
GO
CREATE TABLE #tblItemRelationship
(
ParentItemID int,
ChildItemID int
);
GO
INSERT INTO #tblItemRelationship
(ParentItemID, ChildItemID)
SELECT 1, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 3, 7 UNION ALL
SELECT 4, 8 UNION ALL
SELECT 7, 9 UNION ALL
SELECT 7, 10;
go
with rCTE (
LevelNo,
ParentItemID,
ChildItemID,
ItemDescription,
ItemDate,
LevelDesc,
SortKey
) as (
select
1,
null,
tir1.ParentItemID,
ti.ItemDescription,
ti.ItemDate,
cast(ti.ItemDescription as varchar(8000)),
convert(varchar(8000),ti.ItemDate, 120)
from
#tblItemRelationship tir1
left outer join #tblItemRelationship tir2
on (tir1.ParentItemID = tir2.ChildItemID)
inner join #tblItems ti
on (tir1.ParentItemID = ti.ItemID)
where
tir2.ParentItemID is null
union all
select
r.LevelNo + 1,
ir.ParentItemID,
ir.ChildItemID,
ti.ItemDescription,
ti.ItemDate,
cast(space(4 * (r.LevelNo - 1)) + ti.ItemDescription as varchar(8000)),
cast(cast(r.SortKey as varchar(8000)) + ' ' + convert(varchar(8000),ti.ItemDate, 120) as varchar(8000))
from
rCTE r
inner join #tblItemRelationship ir
on (r.ChildItemID = ir.ParentItemID)
inner join #tblItems ti
on (ir.ChildItemID = ti.ItemID)
)
select * from rCTE order by SortKey;
go
drop table #tblItems;
drop table #tblItemRelationship;
go
July 31, 2012 at 12:35 pm
Thanks very much for that. Looks great to me.
I've been plodding away all afternoon and ended up with this (don't claim to have come up with it, had some help from someone) - any comments?
CREATE TABLE #tblItems
(
ItemID int,
ItemDescription varchar(50),
ItemDate smalldatetime
)
GO
INSERT INTO #tblItems
(ItemID, ItemDescription, ItemDate)
SELECT 1, 'Item 1', '01 Jan 2012' UNION ALL
SELECT 2, 'Item 2', '03 Jan 2012' UNION ALL
SELECT 3, 'Item 3', '05 Jan 2012' UNION ALL
SELECT 4, 'Item 4', '02 Jan 2012' UNION ALL
SELECT 5, 'Item 5', '07 Jan 2012' UNION ALL
SELECT 6, 'Item 6', '09 Jan 2012' UNION ALL
SELECT 7, 'Item 7', '08 Jan 2012' UNION ALL
SELECT 8, 'Item 8', '08 Jan 2012' UNION ALL
SELECT 9, 'Item 9', '12 Jan 2012' UNION ALL
SELECT 10, 'Item 10', '14 Jan 2012' UNION ALL
SELECT 11, 'Item 11', '02 Jan 2012'
GO
CREATE TABLE #tblItemRelationship
(
ParentItemID int,
ChildItemID int
)
GO
INSERT INTO #tblItemRelationship
(ParentItemID, ChildItemID)
SELECT 1, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 3, 7 UNION ALL
SELECT 4, 8 UNION ALL
SELECT 7, 9 UNION ALL
SELECT 7, 10 UNION ALL
SELECT 1, 11
GO
;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000)),
CAST(ROW_NUMBER() OVER(ORDER BY I.ItemDate, I.ItemID) AS varbinary(900)) AS sort_col
FROM
#tblItemRelationship tr
INNER JOIN
#tblItems AS I
ON tr.ChildItemID = I.ItemID
WHERE
tr.ParentItemID = 1
UNION ALL
SELECT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000)),
CAST(lr.sort_col + CAST(ROW_NUMBER() OVER(PARTITION BY tr.ParentItemID ORDER BY I.ItemDate, I.ItemID) AS binary(4)) AS varbinary(900))
FROM
rCTE lr
INNER JOIN
#tblItemRelationship tr
ON tr.ParentItemID = lr.ChildItemID
INNER JOIN
#tblItems AS I
ON tr.ChildItemID = I.ItemID
)
SELECT *
FROM rCTE
ORDER BY sort_col;
GO
DROP TABLE #tblItems
DROP TABLE #tblItemRelationship
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply