How do I write my query to translate a table with parent / child hierarchy into a table with my hierarchy levels in separate columns?
I have a table in SQL Server (from SAP without any changes made I believe) that gives me the structure of groups containing my profit centers. The structure of the table is a classic parent child hierarchy as shown below.
Parent | Child
--------+--------
S-1 | S-11
S-1 | S-12
S-1 | S-13
S-1 | S-14
S-1 | S-15
S-11 | S-111
S-11 | S-112
.. | ..
S-152 | S-1521
S-152 | S-1522
S-1522 | S-15221
I want to write a query that gives me a table where I for each group can find the Level 1, Level 2, Level 3 ect. group. Level 1 is the Top Level (and will always exist) and Level 2 the next. There can be unlimited levels but at this time Level 8 is the highest used.
Group | Level 1 | Level 2 | Level 3 | Level 4 | Level 5
--------+-----------+-----------+-----------+-----------+---------
S-111 | S-1 | S-11 | S-111 | |
S-11211 | S-1 | S-11 | S-112 | S-1121 | S-11211
S-1211 | S-1 | S-12 | S-121 | S-1211 |
S-1212 | S-1 | S-12 | S-121 | S-1212 |
S-122 | S-1 | S-12 | S-122 | |
S-123 | S-1 | S-12 | S-123 | |
S-1311 | S-1 | S-13 | S-131 | S-1311 |
S-1312 | S-1 | S-13 | S-131 | S-1312 |
S-1321 | S-1 | S-13 | S-132 | S-1321 |
S-141 | S-1 | S-14 | S-141 | |
S-151 | S-1 | S-15 | S-151 | |
S-1521 | S-1 | S-15 | S-152 | S-1521 |
S-15221 | S-1 | S-15 | S-152 | S-1522 | S-15221
I have used Google and this page to find the final solutions but haven’t found it yet. But I managed to get this far:
WITH MyTest as
(
SELECT
P.PRCTR_CHILD, P.PRCTR_PARENT,
CAST(P.PRCTR_CHILD AS VARCHAR(MAX)) AS Level
FROM
[IBM_PA_Integration].[dbo].[PRCTRHIER] AS P
WHERE
P.PRCTR_PARENT = 'S-1000' –- S-1000 is a division
UNION ALL
SELECT
P1.PRCTR_CHILD, P1.PRCTR_PARENT,
CAST(P1.PRCTR_CHILD AS VARCHAR(MAX)) + ', ' + M.Level
FROM
[IBM_PA_Integration].[dbo].[PRCTRHIER] AS P1
INNER JOIN
MyTest M ON M.PRCTR_CHILD = P1.PRCTR_PARENT
)
SELECT *
FROM MyTest
WHERE PRCTR_PARENT = 'FS2004' –- FS2004 is the level top level / level above S-1000