Pivot a table to dynamically create new columns without row aggregation

Question

I have a table called [DocType] that lists out various document types as well as the hierarchal parent-child associations. I am wanting to properly PIVOT the group of data from four columns into N number columns. The number of parent-child associations is not static, and I need to account for cases where the categorical level can reach N number.

Below is a sample set of the data. Id is the priary key. CatFolderLevel is the hierarchy level - a value of 1 is the parent, 2 the child, 3 the subchild, and so forth. CatParentId relates back to its parent Id.

╔═════╤═════════════════════════════╤════════════════╤═════════════╗
║ Id  │ CatName                     │ CatFolderLevel │ CatParentId ║
╠═════╪═════════════════════════════╪════════════════╪═════════════╣
║ 66  │ Grades                      │ 1              │ 0           ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 68  │ Transcript                  │ 2              │ 66          ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 129 │ Reports                     │ 1              │ 0           ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 137 │ Evaluation Summary          │ 2              │ 129         ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 317 │ Student Services            │ 1              │ 0           ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 333 │ Programs - Student Services │ 2              │ 317         ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 340 │ Nursing Services            │ 3              │ 333         ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 352 │ Fine Arts                   │ 1              │ 0           ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 357 │ Budget - Fine Arts          │ 2              │ 352         ║
╟─────┼─────────────────────────────┼────────────────┼─────────────╢
║ 358 │ Operational Budget          │ 3              │ 357         ║
╚═════╧═════════════════════════════╧════════════════╧═════════════╝

The desired output would instead look like this:

╔══════════╤══════════════════╤══════════════════════╤═══════════════════╤══════════╤═════════════════════════════╤══════════════════════╤═══════════════════╤══════════╤════════════════════╤══════════════════════╤═══════════════════╗
║ Level1ID │ Level1CatName    │ Level1CatFolderLevel │ Level1CatParentID │ Level2ID │ Level2CatName               │ Level2CatFolderLevel │ Level2CatParentID │ Level3ID │ Level3CatName      │ Level3CatFolderLevel │ Level3CatParentID ║
╠══════════╪══════════════════╪══════════════════════╪═══════════════════╪══════════╪═════════════════════════════╪══════════════════════╪═══════════════════╪══════════╪════════════════════╪══════════════════════╪═══════════════════╣
║ 317      │ Student Services │ 1                    │ 0                 │ 333      │ Programs - Student Services │ 2                    │ 317               │ 340      │ Nursing Services   │ 3                    │ 333               ║
╟──────────┼──────────────────┼──────────────────────┼───────────────────┼──────────┼─────────────────────────────┼──────────────────────┼───────────────────┼──────────┼────────────────────┼──────────────────────┼───────────────────╢
║ 352      │ Fine Arts        │ 1                    │ 0                 │ 357      │ Budget - Fine Arts          │ 2                    │ 352               │ 358      │ Operational Budget │ 3                    │ 357               ║
╟──────────┼──────────────────┼──────────────────────┼───────────────────┼──────────┼─────────────────────────────┼──────────────────────┼───────────────────┼──────────┼────────────────────┼──────────────────────┼───────────────────╢
║ 66       │ Grades           │ 1                    │ 0                 │ 68       │ Transcript                  │ 2                    │ 66                │ NULL     │ NULL               │ NULL                 │ NULL              ║
╟──────────┼──────────────────┼──────────────────────┼───────────────────┼──────────┼─────────────────────────────┼──────────────────────┼───────────────────┼──────────┼────────────────────┼──────────────────────┼───────────────────╢
║ 129      │ Reports          │ 1                    │ 0                 │ 137      │ Evaluation Summary          │ 2                    │ 129               │ NULL     │ NULL               │ NULL                 │ NULL              ║
╚══════════╧══════════════════╧══════════════════════╧═══════════════════╧══════════╧═════════════════════════════╧══════════════════════╧═══════════════════╧══════════╧════════════════════╧══════════════════════╧═══════════════════╝

The method I used to produce the above sample set was with CTEs (see below), but it is static and frankly with displeasure to my attempts, is pretty rudimentary and junk.

;WITH Level3 AS(SELECT
    Id
    ,CatName
    ,CatFolderLevel
    ,CatParentId
FROM
    [DocType]
WHERE
    CatFolderLevel = 3)
,Level2 AS (SELECT
    Id
    ,CatName
    ,CatFolderLevel
    ,CatParentId
FROM
    [DocType]
WHERE
    CatFolderLevel = 2)
,Level1 AS (SELECT
    Id
    ,CatName
    ,CatFolderLevel
    ,CatParentId
FROM
    [DocType]
WHERE
    CatFolderLevel = 1)
SELECT
    Level1.Id AS 'Level1ID'
    ,Level1.CatName AS 'Level1CatName'
    ,Level1.CatFolderLevel 'Level1CatFolderLevel'
    ,Level1.CatParentId 'Level1CatParentID'
    ,Level2.Id AS 'Level2ID'
    ,Level2.CatName AS 'Level2CatName'
    ,Level2.CatFolderLevel AS 'Level2CatFolderLevel'
    ,Level2.CatParentId AS 'Level2CatParentID'
    ,Level3.Id AS 'Level3ID'
    ,Level3.CatName AS 'Level3CatName'
    ,Level3.CatFolderLevel AS 'Level3CatFolderLevel'
    ,Level3.CatParentId AS 'Level3CatParentID'
FROM
    Level3
    JOIN Level2
       ON Level3.CatParentId = Level2.Id
    JOIN Level1
       ON Level2.CatParentId = Level1.Id
UNION ALL
SELECT
    Level1.Id AS 'Level1ID'
    ,Level1.CatName AS 'Level1CatName'
    ,Level1.CatFolderLevel 'Level1CatFolderLevel'
    ,Level1.CatParentId 'Level1CatParentID'
    ,Level2.Id AS 'Level2ID'
    ,Level2.CatName AS 'Level2CatName'
    ,Level2.CatFolderLevel AS 'Level2CatFolderLevel'
    ,Level2.CatParentId AS 'Level2CatParentID'
    ,NULL AS 'Level3ID'
    ,NULL AS 'Level3CatName'
    ,NULL AS 'Level3CatFolderLevel'
    ,NULL AS 'Level3CatParentID'
FROM
    Level2
    JOIN Level1
       ON Level2.CatParentId = Level1.Id

I have explored various usages of PIVOT with dynamic T-SQL, but they all aggregate the data and I am unsure how to properly transform the data dynamically as desired without aggregation.

Can anyone point me in the right direction to produce the results I am seeking?

Answer 1

You can generate left joins as below: You need to provide your table name or you can change in script accordingly. I tally tables to avoid loop.

declare @Query nvarchar(max);
declare @yourtablename nvarchar(50) = '#yourfolder ';

SET @query = 'Select '
;with c1 as  (  select * from (values (1), (1),(1), (1),(1), (1),(1), (1),(1), (1)) v(n)    )--tally table
, c2 as  (  select n1.* from c1 n1, c1 n2, c1 n3, c1 n4 ) 
, cte1 as ( select top(select max(catfolderlevel) m from #yourfolder) RowN = Row_number() over(order by (select null)) from c2 )
select @Query += concat(' l', RowN,'.Id as Level',RowN,'Id,  l',RowN,'.CatName as Level', RowN, 'CatName, l', RowN, '.CatFolderLevel as Level', RowN, 'CatFolder, l', RowN, '.CatParentId as Level', RowN,'CatParentId , ') from cte1

SELECT @Query = left(@query,len(@Query)-2)

Select @query += '  from ' + @yourtablename + ' l1  '
;with c1 as  (  select * from (values (1), (1),(1), (1),(1), (1),(1), (1),(1), (1)) v(n)    )
, c2 as  (  select n1.* from c1 n1, c1 n2, c1 n3, c1 n4 ) 
, cte1 as ( select top(select max(catfolderlevel)-1 m from #yourfolder) RowN = Row_number() over(order by (select null))+1 from c2 )
Select @Query += concat(' left join #yourfolder l',RowN, ' on l', RowN-1,'.id = l', RowN,'.catparentid and l', RowN-1,'.catfolderlevel = ', RowN -1) from cte1

select @Query += ' Where l1.CatFolderLevel = 1'

select @Query  --Check your query
exec sp_executesql @Query

Output:

+----------+------------------+-----------------+-------------------+----------+---------------+-----------------+-----------------------------+----------+--------------------+--------------------+-------------------+---+-----+-----+--------------------+------+------+------+------+------+
| Level1Id |  Level1CatName   | Level1CatFolder | Level1CatParentId | Level2Id | Level2CatName | Level2CatFolder |      Level2CatParentId      | Level3Id |   Level3CatName    |  Level3CatFolder   | Level3CatParentId |   |     |     |                    |      |      |      |      |      |
+----------+------------------+-----------------+-------------------+----------+---------------+-----------------+-----------------------------+----------+--------------------+--------------------+-------------------+---+-----+-----+--------------------+------+------+------+------+------+
|       66 | Grades           |                 |                   |          |               |                 | 1                           |        0 | 68                 | Transcript         |                   |   |     |     | 2                  | 66   | NULL | NULL | NULL | NULL |
|      129 | Reports          |                 |                   |          |               |                 | 1                           |        0 | 137                | Evaluation Summary |                   |   |   2 | 129 | NULL               | NULL | NULL | NULL |      |      |
|      317 | Student Services |                 |                   |        1 |             0 |             333 | Programs - Student Services |        2 | 317                | 340                | Nursing Services  |   |     |   3 | 333                |      |      |      |      |      |
|      352 | Fine Arts        |                 |                   |          |               |               1 | 0                           |      357 | Budget - Fine Arts |                    |                   | 2 | 352 | 358 | Operational Budget |      |      | 3    | 357  |      |
+----------+------------------+-----------------+-------------------+----------+---------------+-----------------+-----------------------------+----------+--------------------+--------------------+-------------------+---+-----+-----+--------------------+------+------+------+------+------+

Generated query for your reference:

Select  l1.Id as Level1Id,  l1.CatName as Level1CatName, l1.CatFolderLevel as Level1CatFolder, l1.CatParentId as Level1CatParentId ,  l2.Id as Level2Id,  l2.CatName as Level2CatName, l2.CatFolderLevel as Level2CatFolder, l2.CatParentId as Level2CatParentId ,  l3.Id as Level3Id,  l3.CatName as Level3CatName, l3.CatFolderLevel as Level3CatFolder, l3.CatParentId as Level3CatParentId from #yourfolder  l1  left join #yourfolder l2 on l1.id = l2.catparentid and l1.catfolderlevel = 1 left join #yourfolder l3 on l2.id = l3.catparentid and l2.catfolderlevel = 2 Where l1.CatFolderLevel = 1

Answer 2

The problem is you are using union instead of left joins. Your query (after the CTE part) can be re--written as

SELECT 
  Level1.Id AS 'Level1ID',
  Level1.CatName AS 'Level1CatName',
  Level1.CatFolderLevel 'Level1CatFolderLevel',
  Level1.CatParentId 'Level1CatParentID',

  Level2.Id AS 'Level2ID',
  Level2.CatName AS 'Level2CatName',
  Level2.CatFolderLevel AS 'Level2CatFolderLevel',
  Level2.CatParentId AS 'Level2CatParentID',

  Level3.Id AS 'Level3ID',
  Level3.CatName AS 'Level3CatName',
  Level3.CatFolderLevel AS 'Level3CatFolderLevel',
  Level3.CatParentId AS 'Level3CatParentID'
FROM Level1
LEFT JOIN Level2 ON Level2.CatParentId = Level1.Id AND Level2.CatFolderLevel = 2
LEFT JOIN Level3 ON Level3.CatParentId = Level2.Id AND Level3.CatFolderLevel = 3

Given this it should be easy to see how to make this dynamic to N levels:

SELECT 
  Level1.Id AS 'Level1ID',
  Level1.CatName AS 'Level1CatName',
  Level1.CatFolderLevel 'Level1CatFolderLevel',
  Level1.CatParentId 'Level1CatParentID',

  -- repeat as needed 
  Level{x}.Id AS 'Level{x}ID',
  Level{x}.CatName AS 'Level{x}CatName',
  Level{x}.CatFolderLevel AS 'Level{x}CatFolderLevel',
  Level{x}.CatParentId AS 'Level{x}CatParentID',

FROM Level1
-- repeat as needed
LEFT JOIN Level{x} ON Level{x}.CatParentId = Level{x-1}.Id AND Level{x}.CatFolderLevel = {x}

Answer 3

This isn't really a PIVOT. This is a dynamic set of JOINS.

I would build a dynamic SQL query where I join N derived tables where each derived table is a CatFolderLevel. Each level joins to the level above it on the ID/ParentId pairing. Use OUTER joins of course, so you still get parents that don't have children.