February 12, 2013 at 9:26 am
Consider this scenario,
Item
ID HierarchyId
1 103
2 104
Hierarchy
HierarchyID Name ParentHierarchyId
100 H1 null
101 H2 100
102 H3 101
103 H4 102
104 H5 null
HierarchyId in Item table is the foreign key to HierarchyId in Hierarchy Table.
ParentHierarchyId is self referenced foreign key.
I need to write a query that will join Item with Hierarchy table and return the foll resultset,
1 H1/H2/H3/H4
2
I need to concatenate the Name in the Hierarchy table for those records that match the hierachy id in Item table.
For e.g. Take ID 1 in Item table, HierachyId is 103. Join this with the HierarchyId in HierarchyTable and recurse on ParentHierarchyId until ParentHierarchyId is null.
So in this case the query should return H1/H2/H3/H4.
February 12, 2013 at 9:48 am
There is nowhere near enough information to offer much help. It seems you could use the techniques described in this article to achieve what you are after. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
If that doesn't help please take a few minutes to read the first article in my signature for best practices when posting a question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2013 at 9:55 am
ID Hid Hid Name PHid
----------- ----------- ----------- ---------- -----------
1 103 103 H4 102
2 104 104 H5 NULL
NULL NULL 100 H1 NULL
NULL NULL 101 H2 100
NULL NULL 102 H3 101
Is this the result you want????
Query I used is
select * from Item full outer join Hier on Item.Hid = Hier.Hid
--Pra:-):-)--------------------------------------------------------------------------------
February 12, 2013 at 10:47 am
I added some more information. Please take a look.
February 12, 2013 at 11:47 am
phbdev (2/12/2013)
I added some more information. Please take a look.
Please read my original post, especially the second part.
There is nowhere near enough information to offer much help. It seems you could use the techniques described in this article to achieve what you are after. http://www.sqlservercentral.com/articles/comma+separated+list/71700/
If that doesn't help please take a few minutes to read the first article in my signature for best practices when posting a question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2013 at 10:33 pm
Something like this perhaps?
DECLARE @Item TABLE (ID INT, [HierarchyId] INT)
INSERT INTO @Item SELECT 1,103 UNION ALL SELECT 2,104
DECLARE @Hierarchy TABLE ([HierarchyID] INT, Name VARCHAR(5), ParentHierarchyId INT)
INSERT INTO @Hierarchy
SELECT 100,'H1',null UNION ALL SELECT 101,'H2',100
UNION ALL SELECT 102,'H3',101 UNION ALL SELECT 103,'H4',102
UNION ALL SELECT 104, 'H5',null
;WITH rCTE AS (
SELECT n=1, a.ID, Name=CAST(b.Name AS VARCHAR(8000)), b.ParentHierarchyID
FROM @Item a
JOIN @Hierarchy b ON a.[HierarchyId] = b.[HierarchyId]
UNION ALL
SELECT n + 1, a.ID, b.Name + '/' + a.Name, b.ParentHierarchyID
FROM rCTE a
JOIN @Hierarchy b ON a.ParentHierarchyID = b.[HierarchyId]
)
SELECT ID, Name
FROM (
SELECT n, ID, Name
,m=MAX(n) OVER (PARTITION BY ID)
FROM rCTE) a
WHERE m=n
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply