Hierarchy (SQL SERVER)

See more:

SQL-Server-2008R2

, +

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