我的輸出如下
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
我需要如下所示的輸出格式，
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
我已嘗試使用以下查詢
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
我在上面的查詢中使用了帶有多個聯合陳述句的 case 運算式來獲取輸出。如何使用 SQL Server 格式化正確的輸出？
誰能幫我解決這個問題
uj5u.com熱心網友回復：
根據發布的示例資料，一個簡單GROUP BY的選項是可能的：
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
結果：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/340105.html
標籤：sql sql-server 查询语句 存储过程