Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a request to get information from a table with a stored procedure that shows all the necessary data for in one line. This table includes a hierarchyID, so I am trying to use the HID methods in it.

    HID hierarchyID,
    ID int (PK),
    CodeName varchar(25),
    ParentCodeID int

As an example, if I execute a SP that queries for all the ancestors of record 15 up to the top level in my table with the current stored procedure, the SP will return the following information:

Level | ID  |   CodeName  |  ParentID
 1      3        C3PN         NULL
 2      13       C13P3         3
 3      15       C15P13        13

Ideally, the report should display like this:

ID  |  Level1  |  Level2  |  Level3  |  Lv1Name  |  Lv2Name  |  Lv3Name
15        3         13         15        C3PN        C13P3      C15P13

The idea is that the single-line report has as many columns as the hierarchyID's levels. (Note that the Level column is not built into the table; I am using GetTable() on the hierarchyID in my SELECT sets.)
I decided a PIVOT would probably be best. Unfortunately, it has to be generated dynamically, and my know-how on dynamic SQL is proving lacking. The best I've been able to get so far is something like this:

15  3       NULL    NULL
15  NULL    13      NULL
15  NULL    NULL    15  

So with that, I'm thinking an UNPIVOT followed by a PIVOT would be the ideal solution, but as of the moment, I'm unsure on how to implement that via dynamic SQL. Any help on this would be greatly appreciated.


EDIT for @bluefeet:

This is what I have for the stored procedure so far:

CREATE PROC [dbo].[PartCodes_ReportQuery]
    @CurrentCode int

DECLARE @T AS TABLE(y int  NOT NULL PRIMARY KEY, L varchar(20) null, c nvarchar(50) null, n varchar(20) null)

    select ID, 'Level'+ CAST(HID.GetLevel() as varchar(20)) as [Level], CodeName , 'Level'+CAST(HID.GetLevel() as varchar(20))+'CodeName' as [LevelCodeName]  FROM PartCodes
    where ( select HID 
        from PartCodes 
        where ID = @CurrentCode ).IsDescendantOf(HID) = 1 

DECLARE @cols AS nvarchar(MAX),
        @cols2 as nvarchar(MAX),
        @pcols as varchar(max),
        @scols as varchar(max),
        @y AS int, @L as varchar(20), @c nvarchar(50), @n nvarchar(20)

SET @L = (SELECT L from @T WHERE y = @y)
SET @c = (select c from @t where y = @y)
SET @n = (SELECT n from @t where y = @y)
SET @cols = N''
SET @cols2 = N''
SET @pcols = N''
SET @scols = N''

    SET @cols = @cols + N',['+CAST(@L AS nvarchar(10))+N']' 
    SET @pcols = @pcols + N',['+CAST(@y AS nvarchar(10))+N']'
    SET @scols = @scols + N','+ N'['+CAST(@y AS nvarchar(10))+N']'+ N' AS '+N'"'+@L+N'"'
    SET @cols2 = @cols2 + N',['+CAST(@c AS nvarchar(10))+N']'+ N' AS '+N'"'+@n+N'"'
    SET @y = (SELECT MIN(y) FROM @T WHERE y > @y)
    SET @c = (select c from @t where y = @y)
    SET @L = (SELECT L from @T WHERE y = @y)
    SET @N = (SELECT N FROM @T WHERE y = @y)

 SET @cols = SUBSTRING(@cols, 2, LEN(@cols))
 SET @pcols = SUBSTRING(@pcols, 2, LEN(@pcols))
 SET @scols = SUBSTRING(@scols, 2, LEN(@scols))
 SET @cols2 = SUBSTRING(@cols2, 2, LEN(@cols2))


DECLARE @sql AS nvarchar(MAX)
SET @sql = 

            N'SELECT ' + cast(@CurrentCode as nvarchar(50)) + ' AS ID, ' + @scols + N'
                SELECT ID, Level, CodeName
                    SELECT HID.GetLevel() AS Level, ID, CodeName 
                     FROM PartCodes where
                                            (SELECT HID
                                            FROM PartCodes
                                            WHERE ID = '+cast(@CurrentCode as nvarchar(50))+').IsDescendantOf(HID) = 1

                ) AS D

            ) AS finunpiv
            PIVOT(min(CodeName) FOR ID IN(' + @pcols + N')) AS P'

PRINT @sql 
EXEC sp_executesql @sql


This yields the results with NULLs that I have above. I'm having trouble understanding this stuff. (I also noticed that when I omitted CodeName from my @sql statement, it did not include the other NULLs--it only had a straight line with the three numbers. As I need the names, however, that's not an option.)

share|improve this question
Paging @bluefeet ... – Phil Mar 6 '14 at 1:33
You stated that you were able to get the result into columns but it has null, can you edit your question and post your attempts at getting the result? – bluefeet Mar 6 '14 at 13:49
Edited to show my current query code. I forgot to leave a comment; sorry about that. – QuizzicalFerret Mar 6 '14 at 15:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.