title: SYS_CONNECT_BY_PATH in SQL Server dynamical use
body: <p>I am currently in a migration project from Oracle Exadata (R.I.P :( ) to SQL Server 2008.</p>
  
  <p>I have a few queries in Oracle which contain the nice <code>SYS_CONNECT_BY_PATH</code> function.</p>
  
  <p>To my problem: I have the current dataset</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>And I need this:</p>
  
  <pre><code>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']
  </code></pre>
  
  <p>Here is the current code used in our Exadata:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I tried to use the STUFF Function or even Pivot but i cant come to a right solution.</p>
  
  <p>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.</p>