'Pivot data based on category and date field

I have to identify the records with check_in category of S1, T1 and P1 in the check-in-order and pivot the rest of the data until we hit the next category. I was able to mark the category but was unsuccessful to pickup the min and max dates of each category as the check_in values are different for every row.

Here is the sample data and the expected results. Appreciate your help.

Source:

GRPNBR      CHECK_IN    Date_of_check_in    CHECK_IN_ORDER  
200-001     S1          1/15/2020           3   
200-001     Y23         3/4/2020            15  
200-001     M56         5/6/2020            17  
200-001     UN          8/31/2020           38  
200-001     T1          10/12/2020          78  
200-001     C T28       11/23/2020          91  
200-001     C M3        1/29/2021           93  
200-001     P1          4/22/2021           94  
200-001     CM9         8/4/2021            95  
200-001     CM10        9/4/2021            97

Expected Result:

GRPNBR  CHECK_IN    POST_CHECK_IN   Date_of_check_in    CHECK_IN_ORDER
200-001 S1          Y23             3/4/2020            15
200-001 S1          M56             5/6/2020            17
200-001 S1          UN              8/31/2020           38
200-001 T1          C T28           11/23/2020          91
200-001 T1          C M3            1/29/2021           93
200-001 P1          CM9             8/4/2021            95
200-001 P1          CM10            9/4/2021            97
sqlsql-serveroracleoracle11gsql-server-2012


Solution 1:[1]

Here is one approach that utilizes the window functions to flag and sequence data and then a simple aggregation for the final results

Example or dbFiddle

with cte1 as ( 
Select *
      ,Grp = sum( case when CHECK_IN like '_1' then 1 else 0 end ) over ( partition by [GRPNBR] order by date_of_check_in)
From YourTable
)
, cte2 as (
Select * 
      ,RN  = row_number() over ( partition by Grp order by date_of_check_in)
     ,_Check_In = max( case when CHECK_IN like '_1' then CHECK_IN end ) over ( partition by Grp )
 From  cte1 
)
Select GRPNBR
      ,Check_In  = _Check_In
      ,Post_Check_In = max( Check_In )
      ,Date_of_check_in = max(Date_of_check_in)
      ,Check_In_Order   = max(Check_In_Order)
 From  cte2
 Where RN>1
 Group By GRPNBR,_Check_In,Grp,RN
 Order By Grp,RN

Results

enter image description here

Solution 2:[2]

Your data

GRPNBR CHECK_IN Date_of_check_in CHECK_IN_ORDER FIELD5
200-001 S1 1/15/2020 3
200-001 Y23 3/4/2020 15
200-001 M56 5/6/2020 17
200-001 UN 8/31/2020 38
200-001 T1 10/12/2020 78
200-001 C T28 11/23/2020 91
200-001 C M3 1/29/2021 93
200-001 P1 4/22/2021 94
200-001 CM9 8/4/2021 95
200-001 CM10 9/4/2021 97

to get value like S1, T1 and P1 ,use following query

select GRPNBR,CHECK_IN,Date_of_check_in
from YourTable
where CHECK_IN like   '_1'
GRPNBR CHECK_IN Date_of_check_in
200-001 S1 2020-01-15
200-001 T1 2020-10-12
200-001 P1 2021-04-22

then you want to join with following query

select GRPNBR,CHECK_IN as POST_CHECK_IN   ,Date_of_check_in,CHECK_IN_ORDER
from YourTable
where CHECK_IN not like   '_1'

with following result to get your desired result

GRPNBR POST_CHECK_IN Date_of_check_in CHECK_IN_ORDER
200-001 Y23 2020-03-04 15
200-001 M56 2020-05-06 17
200-001 UN 2020-08-31 38
200-001 C T28 2020-11-23 91
200-001 C M3 2021-01-29 93
200-001 CM9 2021-08-04 95
200-001 CM10 2021-09-04 97

I used Pivot, Subquery and Case to get your desired result

SELECT A.grpnbr,
       CASE
         WHEN date_of_check_in > s1
              AND date_of_check_in < t1 THEN 'S1'
         WHEN date_of_check_in > t1
              AND date_of_check_in < p1 THEN 'T1'
         WHEN date_of_check_in > p1 THEN 'P1'
       END AS CHECK_IN,
       post_check_in,
       date_of_check_in,
       check_in_order
FROM   (SELECT grpnbr,
               check_in AS POST_CHECK_IN,
               date_of_check_in,
               check_in_order
        FROM   yourtable
        WHERE  check_in NOT LIKE '_1') A
       JOIN (SELECT *
             FROM   (SELECT grpnbr,
                            check_in,
                            date_of_check_in
                     FROM   yourtable
                     WHERE  check_in LIKE '_1') src
                    PIVOT ( Max(date_of_check_in)
                          FOR check_in IN ([S1],
                                           [T1],
                                           [P1]) ) piv) B
         ON A.grpnbr = B.grpnbr

dbfiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions