Transpose Rows Into Columns by Delimiter Column Name and Column Title

I’m slightly stuck on this issue and need some SQL assistance please. Iv got a hierarchy table from someone who needs help transposing it and putting them into actual columns of another table. Here is a sample line of the table post querying it. I don’t have access to adjust anything besides working from this table result:

SELECT ID, Path, Path_Values 
FROM TABLE1

Output:

ID  | Path                      | Path_Data_Values
----+---------------------------+----------------------------------
1   | Root                      | Org
2   | Root / Hemisphere         | Org / North Hemisphere
3   | Root / Hemisphere / State | Org / North Hemisphere / Texas
4   | Root / State              | Org / Texas

The above table can go to around 10 levels deep. Anyways I thankfully know the maximum depth of the final table and I have access to where the results should be stored its just above converting the above results to read the path and decide the appropriate column for it and then inserting the value into the end table.

Visual example of this end table (Desired Result):

ID | Root | Hemisphere           | State | Other_1 | Other_2 | Other_3
---+------+----------------------+-------+---------+---------+----------
 1 | Org  | NULL                 | NULL  | NULL    | NULL    | NULL
 2 | Org  | Northern Hemisphere  | NULL  | NULL    | NULL    | NULL
 3 | Org  | Northern Hemisphere  | Texas | NULL    | NULL    | NULL
 4 | Org  | NULL                 | Texas | NULL    | NULL    | NULL

Answer

This gets you most of the way there. As, however, the definition of your data changes from row to row, then there’s no way to get the data in the order you want if you want to do it dynamically. (for Example should state be in position 2 or 3, as it appears in both? Without knowlegde of your data, or a look up table, that is impossible. This is a dynamic solution, however, it shows you the code it generates to get the results you’re after, if you want to manually code all of your positions.

This also makes use of DelimitedSplit8k_LEAD, as STRING_SPLIT does not supply the ordinal position of a item in a delimited list; making it useless here.

CREATE TABLE dbo.YourTable (ID int,
                            [Path] varchar(8000),
                            Path_Data_Values varchar(8000));
INSERT INTO dbo.YourTable(ID, [Path], Path_Data_Values)
VALUES (1,'Root','Org'),
       (2,'Root / Hemisphere','Org / North Hemisphere'),
       (3,'Root / Hemisphere / State','Org / North Hemisphere / Texas'),
       (4,'Root / State','Org / Texas');

GO

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT YT.ID,' + NCHAR(13) + NCHAR(10) +
           STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) + 
                         N'       MAX(CASE P.Item WHEN ' + QUOTENAME(P.Item,'''') + N' THEN PDV.Item END) AS ' + QUOTENAME(P.Item)
                  FROM dbo.YourTable YT
                       CROSS APPLY (VALUES(REPLACE(YT.[Path],' / ','|')))V([Path])
                       CROSS APPLY dbo.DelimitedSplit8K_LEAD(V.[Path],'|') P
                  GROUP BY P.Item
                  FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + NCHAR(13) + NCHAR(10) +
           N'FROM dbo.YourTable YT' + NCHAR(13) + NCHAR(10) +
           N'     CROSS APPLY (VALUES(REPLACE(YT.[Path],'' / '',''|''),REPLACE(YT.Path_Data_Values,'' / '',''|'')))V([Path],Path_Data_Values)' + NCHAR(13) + NCHAR(10) +
           N'     CROSS APPLY dbo.DelimitedSplit8K_LEAD(V.[Path],''|'') P' + NCHAR(13) + NCHAR(10) +
           N'     CROSS APPLY(SELECT DS.Item FROM dbo.DelimitedSplit8K_LEAD(V.Path_Data_Values,''|'') DS WHERE P.ItemNumber = DS.ItemNumber) PDV' + NCHAR(13) + NCHAR(10) +
           N'GROUP BY YT.ID;';

PRINT @SQL;

EXEC sp_executesql @SQL;