Click here to Skip to main content
15,794,275 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
I have a hierarchy table which looks like this:
RowID   Code       Name             ParentID   Level
1        GC         Gulf Coast        NULL       1
21       GC.1       Texas             1          2
28       GC.1.1     Houston           2          3
34       GC.1.2     Galveston         2          3
67       GC.1.1.1   Hou Ship Channel  3          4 

Would like to know how I can query this data so that the resulting data would appear as such:
RowID   Level1 Name       Level2 Name      Level3 Name      Level4 Name
1        Gulf Coast        NULL             NULL
21       Gulf Coast        Texas            NULL
28       Gull Coast        Texas            Houston
34       Gull Coast        Texas            Galveston
67       Gull Coast        Texas            Houston          Hou Ship Channel

The query I've written only is only returning the last level. Can you please help.

Thanks Jake
Updated 7-Jan-15 12:24pm
PIEBALDconsult 7-Jan-15 16:44pm    
Recursive Common Table Expression?
Sergey Alexandrovich Kryukov 7-Jan-15 17:02pm    
Are you saying we have to fix your query without seeing it? And insufficient information of your database schema?
Thava Rajan 8-Jan-15 3:53am    
is the parent id value is right?
Thava Rajan 8-Jan-15 3:54am    
is the parent id of the 3rd and 4th row is 28

I'd suggest to read this: Hierarchical Data (SQL Server)[^].

Here on CP you'll find a lot of articles about fetching hierarchical data. Use SearchBox on the right-top corner of this site.
Share this answer
I figured out that using a UNION gets me want I need. Thank you all that replied.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900