SYS_CONNECT_BY_PATH in SQL Server dynamical use

Question

I am currently in a migration project from Oracle Exadata (R.I.P :( ) to SQL Server 2008.

I have a few queries in Oracle which contain the nice SYS_CONNECT_BY_PATH function.

To my problem: I have the current dataset

ORDER_ID   ORDER_GROUP_ID   OPERATOR_ID   GROUP_NAME    VALUE_ID    DESCRIPTION
--------------------------------------------------------------------------------------------------------------
1             10000            3          USER_ID       not null    'panel_id or msisdn_anonym of user'
2             10000            3          MISSING_FLAG     0        'data for extrapolation are not missing' 
3             10000            3          MISSING_FLAG     1        'data for extrapolation are missing'
5             10000            3          PANEL_FLAG       0        'source of user: no panel'
5             10000            3          PANEL_FLAG       1        'source of user: panel'
6             10000            3          ACTIVE_FLAG      0        'not active user'
7             10000            3          ACTIVE_FLAG      1        'active user'
1             10000            5          USER_ID      not null     'panel_id or msisdn_anonym of user'
2             10000            5          MISSING_FLAG     0        'data for extrapolation are not missing'
3             10000            5          MISSING_FLAG     1        'data for extrapolation are missing'  
5             10000            5          PANEL_FLAG       0        'source of user: no panel'
5             10000            5          PANEL_FLAG       1        'source of user: panel'
6             10000            5          ACTIVE_FLAG      0        'not active user'
7             10000            5          ACTIVE_FLAG      1        'active user'

And I need this:

ORDER_GROUP_ID  ORDER_ID    OPERATOR_ID GROUP_NAME  VALUE_DESCRIPTION
---------------------------------------------------------------------------------------------
10000           1           3             USER_ID               [not null='panel_id or msisdn_anonym of user']
10000           3           3             MISSING_FLAG          [0='data for extrapolation are not missing'] [1='data for extrapolation are missing']
10000           5           3             PANEL_FLAG            [0='source of user: no panel'] [1='source of user: panel']
10000           7           3             ACTIVE_FLAG           [0='not active user'] [1='active user']
10000           1           5             USER_ID               [not null='panel_id or msisdn_anonym of user']
10000           3           5             MISSING_FLAG          [0='data for extrapolation are not missing'] [1='data for extrapolation are missing']
10000           5           5             PANEL_FLAG            [0='source of user: no panel'] [1='source of user: panel']
10000           7           5             ACTIVE_FLAG           [0='not active user'] [1='active user']

Here is the current code used in our Exadata:

SELECT
  order_group_id, order_id, operator_id, group_Name, value_description
FROM 
   (SELECT
        order_id, operator_id, group_Name, level
        , seq, cnt
        , trim (REPLACE (SYS_CONNECT_BY_PATH(value_description, '#'),  '#', ' ' )) AS value_description
        , order_group_id
    FROM 
        (SELECT
            order_id, operator_id, group_Name, VALUE_ID, description, '['||VALUE_ID||'='||description||']' AS value_description
      , row_number() OVER ( PARTITION BY operator_id, group_Name ORDER BY VALUE_ID, description ) seq
            , count(*) OVER ( PARTITION BY operator_id, group_Name ) cnt
            , order_group_id
         FROM 
            NIS_MDM.EPO_FUS_USER_GROUPS_V)
    where level = cnt
    start with seq = 1
    CONNECT BY
    PRIOR operator_id = operator_id and prior group_Name = group_Name
    AND PRIOR seq = seq - 1
  )
  order by operator_id, order_id

I tried to use the STUFF Function or even Pivot but i cant come to a right solution.

I'd be so happy if someone of you can help me. I feel like i searched for the whole internet in different languages but nobody solved or had a smiliar problem to mine.

Answer 1

Well, you can try this query.

Check it in SQL Fiddle

SELECT order_group_id
    ,order_id
    ,operator_id
    ,group_Name
    ,value_description
FROM (
    (
        SELECT order_group_id
            ,order_id
            ,seq = row_number() OVER (
                ORDER BY operator_id
                    ,group_Name
                )
        FROM MyTab
        ) tab INNER JOIN (
        SELECT operator_id
            ,group_Name
            ,row_number() OVER (
                ORDER BY operator_id
                    ,group_Name
                ) seq
            ,STUFF((
                    SELECT ' ' + '[' + VALUE_ID + '=' + [description] + ']'
                    FROM MyTab v
                    WHERE v.operator_id = A.OPERATOR_ID
                        AND v.GROUP_NAME = A.GROUP_NAME
                    FOR XML PATH('')
                        ,TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS value_description
        FROM (
            SELECT order_id
                ,operator_id
                ,group_Name
                ,VALUE_ID
                ,description
                ,order_group_id
            FROM MyTab
            ) a
        GROUP BY operator_id
            ,group_Name
        ) t ON tab.seq = t.seq
    )