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