Sql Server How To Decollapse Data

I want to decollapse data with in same group. let me explain by example. Demo data: +-------------+----+------------+-----------+ | Primarykey | ID | START_Point| STOP_Point| +--

Solution 1:

As a starting point you can use the following query:

SELECT ID, Idx, StartStop, 
       LEAD(Idx) OVER (PARTITIONBY ID ORDERBY IdxRowNum) AS NextIdx,
       LEAD(StartStop) OVER (PARTITIONBY ID ORDERBY IdxRowNum) AS NextStartStop
FROM (
    SELECT ID, Idx, StartStop, ROW_NUMBER() OVER (PARTITIONBY ID ORDERBY Idx, StartStop) AS IdxRowNum                         
    FROM
      (SELECT ID, START_Point, STOP_Point
       FROM@T) Src
    UNPIVOT (
      Idx FOR StartStop IN (START_Point, STOP_Point)
    ) AS Unpvt
) t

to make a table of start - end points for each time span of every ID contained in your original table.

Using the above query as a basis you can get all existing intervals for each ID:

SELECTDISTINCT ID, StartIdx, StopIdx
FROM
(
    SELECT ID, 
           CASEWHEN StartStop ='START_Point'AND NextStartStop ='START_Point'THENCASEWHEN NextIdx > Idx THEN Idx ELSENULLENDWHEN StartStop ='STOP_Point'AND NextStartStop ='STOP_Point'THENCASEWHEN NextIdx > Idx THEN Idx +1ELSENULLENDWHEN StartStop ='STOP_Point'AND NextStartStop ='START_Point'THENCASEWHEN NextIdx > Idx THEN Idx +1ELSENULLENDWHEN StartStop ='START_Point'AND NextStartStop ='STOP_Point'THEN Idx           
           ENDAS StartIdx, 
           CASEWHEN StartStop ='START_Point'AND NextStartStop ='START_Point'THENCASEWHEN NextIdx > Idx THEN NextIdx -1ELSENULLENDWHEN StartStop ='STOP_Point'AND NextStartStop ='STOP_Point'THENCASEWHEN NextIdx > Idx THEN NextIdx ELSENULLENDWHEN StartStop ='START_Point'AND NextStartStop ='STOP_Point'THEN NextIdx
             WHEN StartStop ='STOP_Point'AND NextStartStop ='START_Point'THENCASEWHEN NextIdx = Idx +1THEN NextIdx WHEN NextIdx > Idx THEN NextIdx-1ELSENULLENDELSE Idx
           ENDAS StopIdx
    FROM
    (
        SELECT ID, Idx, StartStop, 
               LEAD(Idx) OVER (PARTITIONBY ID ORDERBY IdxRowNum) AS NextIdx,
               LEAD(StartStop) OVER (PARTITIONBY ID ORDERBY IdxRowNum) AS NextStartStop
        FROM (
            SELECT ID, Idx, StartStop, ROW_NUMBER() OVER (PARTITIONBY ID ORDERBY Idx, StartStop) AS IdxRowNum                         
            FROM
              (SELECT ID, START_Point, STOP_Point
               FROM@T) Src
            UNPIVOT (
              Idx FOR StartStop IN (START_Point, STOP_Point)
            ) AS Unpvt
        ) t
    ) s
) u
WHERE StartIdx ISNOTNULLAND StopIdx ISNOTNULL

The output from the above query is:

ID  StartIdx StopIdx
-----------------------
1   1        1
1   2        4
1   5        5
1   6        10
1   11       15
2   1        1
2   2        2
2   3        4
2   5        5
2   6        10
2   11       20

Using the previous query in a CTE and performing a CROSS APPLY finally gets you what you want:

; WITH IntervalsByID AS
(
    SELECTDISTINCT ID, StartIdx, StopIdx
    FROM
    (
        SELECT ID, 
               CASEWHEN StartStop ='START_Point'AND NextStartStop ='START_Point'THENCASEWHEN NextIdx > Idx THEN Idx ELSENULLENDWHEN StartStop ='STOP_Point'AND NextStartStop ='STOP_Point'THENCASEWHEN NextIdx > Idx THEN Idx +1ELSENULLENDWHEN StartStop ='STOP_Point'AND NextStartStop ='START_Point'THENCASEWHEN NextIdx > Idx THEN Idx +1ELSENULLENDWHEN StartStop ='START_Point'AND NextStartStop ='STOP_Point'THEN Idx           
               ENDAS StartIdx, 
               CASEWHEN StartStop ='START_Point'AND NextStartStop ='START_Point'THENCASEWHEN NextIdx > Idx THEN NextIdx -1ELSENULLENDWHEN StartStop ='STOP_Point'AND NextStartStop ='STOP_Point'THENCASEWHEN NextIdx > Idx THEN NextIdx ELSENULLENDWHEN StartStop ='START_Point'AND NextStartStop ='STOP_Point'THEN NextIdx
                 WHEN StartStop ='STOP_Point'AND NextStartStop ='START_Point'THENCASEWHEN NextIdx = Idx +1THEN NextIdx WHEN NextIdx > Idx THEN NextIdx-1ELSENULLENDELSE Idx
               ENDAS StopIdx
        FROM
        (
            SELECT ID, Idx, StartStop, 
                   LEAD(Idx) OVER (PARTITIONBY ID ORDERBY IdxRowNum) AS NextIdx,
                   LEAD(StartStop) OVER (PARTITIONBY ID ORDERBY IdxRowNum) AS NextStartStop
            FROM (
                SELECT ID, Idx, StartStop, ROW_NUMBER() OVER (PARTITIONBY ID ORDERBY Idx, StartStop) AS IdxRowNum                         
                FROM
                  (SELECT ID, START_Point, STOP_Point
                   FROM@T) Src
                UNPIVOT (
                  Idx FOR StartStop IN (START_Point, STOP_Point)
                ) AS Unpvt
            ) t
        ) s
    ) u
    WHERE StartIdx ISNOTNULLAND StopIdx ISNOTNULL
)
SELECT t.PrimaryKey, t.ID, s.StartIdx, s.StopIdx
FROM@TAS t
CROSS APPLY 
( 
   SELECT*FROM IntervalsByID
   WHERE ID = t.ID AND t.START_Point <= StartIdx AND t.STOP_Point >= StopIdx
) s   
ORDERBY PrimaryKey, StartIdx

Output:

    PrimaryKey  ID  StartIdx    StopIdx
   --------------------------------------1111112411551161021243155415551555161051111562557222821182228234825582610921192229234925592610921120