Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

CREATE TABLE PartCodes as
(
    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:

ID  LEVEL1  LEVEL2  LEVEL3
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
)
AS
SET NOCOUNT ON

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


    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 
END 



BEGIN
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 @y = (SELECT MIN(y) FROM @T)
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''

WHILE @y IS NOT NULL
 BEGIN
    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)
 END

 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))

END 


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

            N'SELECT ' + cast(@CurrentCode as nvarchar(50)) + ' AS ID, ' + @scols + N'
            FROM 
            (   
                SELECT ID, Level, CodeName
                FROM
                (
                    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
END

GO  

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 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 at 13:49
    
Edited to show my current query code. I forgot to leave a comment; sorry about that. –  QuizzicalFerret Mar 6 at 15:26

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.