How to format SQL query output using case expression-CodePudding

My output is given below

WFH_ID FromTime1 ToTime1 FromTime2 ToTime2 FromTime3 ToTime3 FromTime4 FromTime4
  1     NULL      NULL     NULL      NULL    NULL     NULL      NULL     NULL
  1     NULL      NULL     NULL      NULL    NULL     NULL    11:45 AM  12:30 PM
  1     NULL      NULL     NULL      NULL  10:45 AM  11:30 AM   NULL     NULL
  1     NULL      NULL    9:45 AM  10:30 AM  NULL     NULL      NULL     NULL
  1    8:30 AM   9:30 AM   NULL      NULL    NULL     NULL      NULL     NULL
  2     NULL      NULL     NULL      NULL    NULL     NULL      NULL     NULL
  2     NULL      NULL     NULL      NULL    NULL     NULL    12:00 AM  12:45 PM
  2     NULL      NULL     NULL      NULL  10:45 AM  11:30 AM   NULL     NULL
  2     NULL      NULL    9:45 AM  10:30 AM  NULL     NULL      NULL     NULL
  2    8:15 AM   9:30 AM   NULL      NULL    NULL     NULL      NULL     NULL

I need output format like given below,

WFH_ID FromTime1 ToTime1 FromTime2 ToTime2 FromTime3 ToTime3 FromTime4 FromTime4
  1     8:30 AM  9:30 AM  9:45 AM  10:30 AM 10:45 AM  11:30 AM 11:45 AM  12:30 PM
  2     8:15 AM  9:30 AM  9:45 AM  10:30 AM 10:45 AM  11:30 AM 12:00 AM  12:30 PM

I have tried with the below query

SELECT WFH_Id
            , 
            CASE
            WHEN Slot_Count=4 AND Row_NO=1
                THEN FromTime
            END AS FromTime_1,
            CASE
            WHEN Slot_Count=4 AND Row_NO=1
                THEN ToTime
            END AS ToTime_1,

            CASE
            WHEN Slot_Count=4 AND Row_NO=2
                THEN FromTime
            END AS FromTime_2,
            CASE
            WHEN Slot_Count=4 AND Row_NO=2
                THEN ToTime
            END AS ToTime_2,

            CASE
            WHEN Slot_Count=4 AND Row_NO=3
                THEN FromTime
            END AS FromTime_3,
            CASE
            WHEN Slot_Count=4 AND Row_NO=3
                THEN ToTime
            END AS ToTime_3,

            CASE
            WHEN Slot_Count=4 AND Row_NO=4
                THEN FromTime
            END AS FromTime_4,
            CASE
            WHEN Slot_Count=4 AND Row_NO=4
                THEN ToTime
            END AS ToTime_4
    FROM #tempMorning 
   UNION
   SELECT WFH_Id
            , 
            CASE
            WHEN Slot_Count=3 AND Row_NO=1
                THEN FromTime
            END AS FromTime_1,
            CASE
            WHEN Slot_Count=3 AND Row_NO=1
                THEN ToTime
            END AS ToTime_1,

            CASE
            WHEN Slot_Count=3 AND Row_NO=2
                THEN FromTime
            END AS FromTime_2,
            CASE
            WHEN Slot_Count=3 AND Row_NO=2
                THEN ToTime
            END AS ToTime_2,

            CASE
            WHEN Slot_Count=3 AND Row_NO=3
                THEN FromTime
            END AS FromTime_3,
            CASE
            WHEN Slot_Count=3 AND Row_NO=3
                THEN ToTime
            END AS ToTime_3,

            CASE
            WHEN Slot_Count=3 AND Row_NO=4
                THEN FromTime
            END AS FromTime_4,
            CASE
            WHEN Slot_Count=3 AND Row_NO=4
                THEN ToTime
            END AS ToTime_4
    FROM #tempMorning 
    UNION
   SELECT WFH_Id
            , 
            CASE
            WHEN Slot_Count=2 AND Row_NO=1
                THEN FromTime
            END AS FromTime_1,
            CASE
            WHEN Slot_Count=2 AND Row_NO=1
                THEN ToTime
            END AS ToTime_1,

            CASE
            WHEN Slot_Count=2 AND Row_NO=2
                THEN FromTime
            END AS FromTime_2,
            CASE
            WHEN Slot_Count=2 AND Row_NO=2
                THEN ToTime
            END AS ToTime_2,

            CASE
            WHEN Slot_Count=2 AND Row_NO=3
                THEN FromTime
            END AS FromTime_3,
            CASE
            WHEN Slot_Count=2 AND Row_NO=3
                THEN ToTime
            END AS ToTime_3,

            CASE
            WHEN Slot_Count=2 AND Row_NO=4
                THEN FromTime
            END AS FromTime_4,
            CASE
            WHEN Slot_Count=2 AND Row_NO=4
                THEN ToTime
            END AS ToTime_4
    FROM #tempMorning 
    UNION
   SELECT WFH_Id
            , 
            CASE
            WHEN Slot_Count=1 AND Row_NO=1
                THEN FromTime
            END AS FromTime_1,
            CASE
            WHEN Slot_Count=1 AND Row_NO=1
                THEN ToTime
            END AS ToTime_1,

            CASE
            WHEN Slot_Count=1 AND Row_NO=2
                THEN FromTime
            END AS FromTime_2,
            CASE
            WHEN Slot_Count=1 AND Row_NO=2
                THEN ToTime
            END AS ToTime_2,

            CASE
            WHEN Slot_Count=1 AND Row_NO=3
                THEN FromTime
            END AS FromTime_3,
            CASE
            WHEN Slot_Count=1 AND Row_NO=3
                THEN ToTime
            END AS ToTime_3,

            CASE
            WHEN Slot_Count=1 AND Row_NO=4
                THEN FromTime
            END AS FromTime_4,
            CASE
            WHEN Slot_Count=1 AND Row_NO=4
                THEN ToTime
            END AS ToTime_4
    FROM #tempMorning

I have used case expression with multiple union statement in the above query for getting output. How to format correct output using SQL Server?

Can anyone help me to solve this issue

CodePudding user response：

Based on the posted sample data, a simple GROUP BY is a possible option:

SELECT 
   WFH_ID, 
   MAX(FromTime1) AS FromTime1,
   MAX(ToTime1) AS ToTime1, 
   MAX(FromTime2) AS FromTime2,
   MAX(ToTime2) AS ToTime2, 
   MAX(FromTime3) AS FromTime3,
   MAX(ToTime3) AS ToTime3, 
   MAX(FromTime4) AS FromTime4,
   MAX(ToTime4) AS ToTime4
FROM (VALUES
  (1, NULL,    NULL,    NULL,    NULL,    NULL,    NULL,    NULL,    NULL),
  (1, NULL,    NULL,    NULL,    NULL,    NULL,    NULL,    '11:45', '12:30'),
  (1, NULL,    NULL,    NULL,    NULL,    '10:45', '11:30', NULL,    NULL),
  (1, NULL,    NULL,    '09:45', '10:30', NULL,    NULL,    NULL,    NULL),
  (1, '08:30', '09:30', NULL,    NULL,    NULL,    NULL,    NULL,    NULL),
  (2, NULL,    NULL,    NULL,    NULL,    NULL,    NULL,    NULL,    NULL),
  (2, NULL,    NULL,    NULL,    NULL,    NULL,    NULL,    '12:00', '12:45'),
  (2, NULL,    NULL,    NULL,    NULL,    '10:45', '11:30', NULL,    NULL),
  (2, NULL,    NULL,    '09:45', '10:30', NULL,    NULL,    NULL,    NULL),
  (2, '08:15', '09:30', NULL,    NULL,    NULL,    NULL,    NULL,    NULL)
) t (WFH_ID, FromTime1, ToTime1, FromTime2, ToTime2, FromTime3, ToTime3, FromTime4, ToTime4)
GROUP BY WFH_ID

Result:

WFH_ID FromTime1 ToTime1 FromTime2 ToTime2 FromTime3 ToTime3 FromTime4 ToTime4
1      08:30     09:30   09:45     10:30   10:45     11:30   11:45     12:30
2      08:15     09:30   09:45     10:30   10:45     11:30   12:00     12:45