#sql #sql-server #tsql #stored-procedures
Вопрос:
Мои результаты приведены ниже
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?
Может ли кто-нибудь помочь мне решить эту проблему
Комментарии:
1. Пожалуйста, предоставьте примеры данных (в идеале DDL DML), которые дают эти результаты. Гарантируется ли показанный шаблон? т. е. каждая строка будет следующей по времени?
2. Почему нет
SELECT WFH_ID, MAX(FromTime1) AS FromTime1, ... FROM ... GROUP BY WFH_ID?3. Пожалуйста, не добавляйте кучу бессмысленных точек в нижней части вашего вопроса. Сохраняйте весь контент полезным.
4. Я не могу отправить свой вопрос. Вот почему я поставил точку. получает ошибку проверки из стека по потоку(проверка-это вопросы, содержащие много кода)
5. Я дал Максу. проблема решена
Ответ №1:
На основе опубликованных выборочных данных возможен простой 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