NULLS в выбранных подзапросах

Question

У меня есть две таблицы. Один показывает рабочие имена шаблонов и один показывает часы, отработанные для каждого дня с идентификатором шаблона в каждом столбце. Я хочу, чтобы это связывало так, чтобы часы для каждого дня появились в одной строке. Для каждого шаблона существует максимум 28, поэтому любые шаблоны, у которых меньше дней, дополнительные столбцы должны возвращать ноль. Я использовал следующее:

SELECT  DISTINCT    tn.pat_nm                                                       AS 'Pattern Name',
                    tn.pat_id                                                       AS 'Pattern ID',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=1)   AS 'Day 1',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=2)   AS 'Day 2',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=3)   AS 'Day 3',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=4)   AS 'Day 4',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=5)   AS 'Day 5',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=6)   AS 'Day 6',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=7)   AS 'Day 7',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=8)   AS 'Day 8',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=9)   AS 'Day 9',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=10)  AS 'Day 10',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=11)  AS 'Day 11',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=12)  AS 'Day 12',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=13)  AS 'Day 13',
                    (select format(td.pat_day_hrs,'HH:mm') where td.pat_day_no=14)  AS 'Day 14'

FROM                trentadm.tpat_nm tn
                    JOIN trentadm.tpat_det td
                        ON td.pat_id=tn.pat_id


ORDER BY            tn.pat_nm

Я получаю результат ниже

Есть ли способ получить все часы на одной строке для каждого шаблона?

Заранее спасибо!

Answer 1

Попробуй использовать PIVOT

SELECT *
FROM
  (
    SELECT tn.pat_nm,tn.pat_id,td.pat_day_no,format(td.pat_day_hrs,'HH:mm') d
    FROM trentadm.tpat_nm tn
    JOIN trentadm.tpat_det td ON td.pat_id=tn.pat_id
  ) q PIVOT(MAX(d) FOR pat_day_no IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14])) p

И после этого вы можете заменить этикетки

SELECT
  pat_nm 'Pattern Name',
  pat_id 'Pattern ID',
  [1] 'Day 1',
  [2] 'Day 2',
  [3] 'Day 3',
  [4] 'Day 4',
  [5] 'Day 5',
  [6] 'Day 6',
  [7] 'Day 7',
  [8] 'Day 8',
  [9] 'Day 9',
  [10] 'Day 10',
  [11] 'Day 11',
  [12] 'Day 12',
  [13] 'Day 13',
  [14] 'Day 14'
FROM
  (
    SELECT tn.pat_nm,tn.pat_id,td.pat_day_no,format(td.pat_day_hrs,'HH:mm') d
    FROM trentadm.tpat_nm tn
    JOIN trentadm.tpat_det td ON td.pat_id=tn.pat_id
  ) q PIVOT(MAX(d) FOR pat_day_no IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14])) p

Answer 2

Попробуйте вот так:

SELECT  DISTINCT    tn.pat_nm                                                       AS 'Pattern Name',
                    tn.pat_id                                                       AS 'Pattern ID',
                    MAX(CASE WHEN td.pat_day_no=1 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 1',
                    MAX(CASE WHEN td.pat_day_no=2 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 2',
                    MAX(CASE WHEN td.pat_day_no=3 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 3',
                    MAX(CASE WHEN td.pat_day_no=4 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 4',
                    MAX(CASE WHEN td.pat_day_no=5 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 5',
                    MAX(CASE WHEN td.pat_day_no=6 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 6',
                    MAX(CASE WHEN td.pat_day_no=7 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 7',
                    MAX(CASE WHEN td.pat_day_no=8 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 8',
                    MAX(CASE WHEN td.pat_day_no=9 THEN format(td.pat_day_hrs,'HH:mm') END)   AS 'Day 9',
                    MAX(CASE WHEN td.pat_day_no=10 THEN format(td.pat_day_hrs,'HH:mm') END)  AS 'Day 10',
                    MAX(CASE WHEN td.pat_day_no=11 THEN format(td.pat_day_hrs,'HH:mm') END)  AS 'Day 11',
                    MAX(CASE WHEN td.pat_day_no=12 THEN format(td.pat_day_hrs,'HH:mm') END)  AS 'Day 12',
                    MAX(CASE WHEN td.pat_day_no=13 THEN format(td.pat_day_hrs,'HH:mm') END)  AS 'Day 13',
                    MAX(CASE WHEN td.pat_day_no=14 THEN format(td.pat_day_hrs,'HH:mm') END)  AS 'Day 14'
FROM                trentadm.tpat_nm tn
JOIN trentadm.tpat_det td
    ON td.pat_id=tn.pat_id
GROUP BY tn.pat_nm, tn.pat_id
ORDER BY tn.pat_nm