Ssrs: Recursive Parent Child

I want to create hierarchical report in SSRS using this simple table? Could you please help me? I follow the tutorial here: https://www.mssqltips.com/sqlservertip/1939/working-with

Solution 1:

This should work for any depth (assuming you don't pass VARCHAR(50) for SORT_PATH). The 'Trick' while you are going down the hierarchy you build a string that you can sort by (SORT_PATH). At the end, we can use REPLICATE() with our BOMLevel to indent with something (in this case a hyphen).

SET NOCOUNT ON;

DECLARE@ComponentASTABLE (
         COL1 VARCHAR(50) ,
         COL2 VARCHAR(50)
        );

INSERTINTO@Component
        ( COL1, COL2 )
VALUES  ( NULL,     'TEST01' ),  -- ADDED
        ( 'TEST01', 'TEST02' ),
        ( 'TEST01', 'TEST03' ),
        ( 'TEST01', 'TEST04' ),
        ( 'TEST02', 'LAB'    ),
        ( 'TEST02', 'STL40'  ),
        ( 'TEST03', 'LABSTL' ),
        ( 'TEST03', 'STLSCH40' ),
        ( 'TEST04', 'LABSTL' ),
        ( 'TEST04', 'FLG41'  ),
        ( 'TEST04', 'STLSCH40' )

;
WITH    tBOM
          AS ( SELECT   A.COL1 , -- PARENT
                        A.COL2 , -- CURRENT0AS "BOMLevel",
                        CAST(A.COL2 ASVARCHAR(50)) AS SORT_PATH
               FROM@Component A
               WHERE    A.COL1 ISNULLUNIONALLSELECT   C.COL1 ,
                        C.COL2 ,
                        BOMLevel +1,
                        CAST(SORT_PATH +'.'+ C.COL2 ASVARCHAR(50)) AS SORT_PATH
               FROM@Component C
                        INNERJOIN tBOM ON tBOM.COL2 = C.COL1
             )
     SELECT COL1 ,
            COL2 ,
            BOMLevel,
            SORT_PATH,
            REPLICATE('-', tBOM.BOMLevel) + COL2 AS DISPLAY_PATH
     FROM   tBOM
     ORDERBY SORT_PATH

Solution 2:

This seems like kind of a funky way to do it but I can't really think of a good way to output the results in the format you're expecting:

CREATETABLE #table (Col1 NVARCHAR(100), Col2 NVARCHAR(100))
INSERT #tableVALUES ('TEST01', 'TEST02')
, ('TEST01', 'TEST03')
, ('TEST01', 'TEST04')
, ('TEST02', 'LAB')
, ('TEST02', 'STL40')
, ('TEST03', 'LABSTL')
, ('TEST03', 'STLSCH40')
, ('TEST04', 'LABSTL')
, ('TEST04', 'FLG41')
, ('TEST04', 'STLSCH40');

WITH CTE1 AS (
    SELECT C1.Col1 C1
        , C2.Col2 C2
        , C3.Col2 C3
        , ROW_NUMBER() OVER (ORDERBY C1.Col1, C2.Col2, C3.Col2) RN
    FROM (SELECT Col1 FROM #tableWHERE Col1 NOTIN (SELECT Col2 FROM #table)) C1
    LEFTJOIN #table C2 ON C2.Col1 = C1.Col1
    LEFTJOIN #table C3 ON C3.Col1 = C2.Col2
    GROUPBY C1.Col1, C2.Col2, C3.Col2)
, CTE2 AS (
    SELECTCASEWHEN RN =1THEN C1 ELSENULLEND C1
    , CASEWHEN RN =1THENCAST('-'+ C2 AS NVARCHAR(100))
        WHEN (SELECT C2 FROM CTE1 WHERE RN = C.RN-1) = C2 THENNULLELSECAST('-'+ C2 AS NVARCHAR(100))  END C2
    , CASEWHEN RN =1THENCAST('----'+ C3 AS NVARCHAR(100))
        WHEN (SELECT C3 FROM CTE1 WHERE RN = C.RN-1) = C3 THENNULLELSECAST('----'+ C3 AS NVARCHAR(100)) END C3
    FROM CTE1 C)
SELECT Col1
FROM CTE2
UNPIVOT (Col1 FOR Col IN (C1, C2, C3)) a

DROPTABLE #table

This outputs:

+--------------+
|     Col1     |
+--------------+
| TEST01       |
| -TEST02      |
| ----LAB      |
| ----STL40    |
| -TEST03      |
| ----LABSTL   |
| ----STLSCH40 |
| -TEST04      |
| ----FLG41    |
| ----LABSTL   |
| ----STLSCH40 |
+--------------+

... But it makes the assumption that the maximum depth of any parent is 2 (as per the example). If you go deeper than that and you're not sure how deep it goes, then you'd need to use dynamic SQL to produce a similar result. Of note, I've answered a similar question to this in the following link, if you want to see how to produce a HTML list as output from a parent/child table: https://stackoverflow.com/a/34445701/5552667