How to display (recursive) data-set in a particular manner?-CodePudding

my brain may not be working today... but I'm trying to get a dataset to be arranged in a particular way. It's easier to show what I mean.

I have a dataset like this:

CREATE TABLE #EXAMPLE (
    ID CHAR(11)
    , ORDER_ID INT
    , PARENT_ORDER_ID INT
    );

INSERT INTO #EXAMPLE VALUES
      ('27KJKR8K3TP', 19517, 0)
    , ('27KJKR8K3TP', 10615, 0)
    , ('27KJKR8K3TP', 83364, 19517)
    , ('27KJKR8K3TP', 96671, 10615)
    , ('TXCMK9757JT', 92645, 0)
    , ('TXCMK9757JT', 60924, 92645);

SELECT * FROM #EXAMPLE;

DROP TABLE #EXAMPLE;

The PARENT_ORDER_ID field refers back to other orders on the given ID. E.g. ID TXCMK9757JT has order 60924 which is a child order of 92645, which is a separate order on the ID. The way I need this dataset to be arranged is like this:

CREATE TABLE #EXAMPLE (
    ID CHAR(11)
    , ORDER_ID INT
    , CHILD_ORDER_ID INT
    );

INSERT INTO #EXAMPLE VALUES
      ('27KJKR8K3TP', 19517, 19517)
    , ('27KJKR8K3TP', 19517, 83364)
    , ('27KJKR8K3TP', 10615, 10615)
    , ('27KJKR8K3TP', 10615, 96671) 
    --, ('27KJKR8K3TP', 83364, 83364)
    --, ('27KJKR8K3TP', 96671, 96671)
    , ('TXCMK9757JT', 92645, 92645)
    , ('TXCMK9757JT', 92645, 60924)
    --, ('TXCMK9757JT', 60924, 60924)
    ;

SELECT * FROM #EXAMPLE;

DROP TABLE #EXAMPLE;

In this arrangement of the data set, instead of PARENT_ORDER_ID field there is CHILD_ORDER_ID, which basically lists every single ORDER_ID falling under a given ORDER_ID, including itself. I ultimately would like to have the CHILD_ORDER_ID field be the key for the data set, having only unique values (so that's why I've commented out the CHILD_ORDER_IDs that would only contain themselves, because they have a parent order ID which already contains them).

Any advice on how to achieve the described transformation of the data set would be greatly appreciated! I've tried recursive CTEs and different join statements but I'm not quite getting what I want. Thank you!

CodePudding user response：

You can try to use CTE recursive first, then you will get a result to show all Id hierarchy then use CASE WHEN judgment the logic.

;WITH  CTE AS (
  SELECT ID,ORDER_ID,PARENT_ORDER_ID
  FROM #EXAMPLE 
  WHERE PARENT_ORDER_ID = 0
  UNION ALL
  SELECT c.Id,e.ORDER_ID,e.PARENT_ORDER_ID
  FROM CTE c
  INNER JOIN #EXAMPLE e
  ON c.ORDER_ID = e.PARENT_ORDER_ID AND c.Id = e.Id
)
SELECT ID,
      (CASE WHEN PARENT_ORDER_ID = 0 THEN ORDER_ID ELSE PARENT_ORDER_ID END) ORDER_ID,
      ORDER_ID CHILD_ORDER_ID
FROM CTE
ORDER BY ID

sqlfiddle