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