Search code examples
sqlt-sqlsubquerynested-select

NULLS in select subqueries

I have two tables. One showing working pattern names and one showing the hours worked for each day with the pattern id in each column. I want it to link so that the hours for each day appear on one row. For each pattern there is a maximum of 28 so any patterns which have less days the extra columns should return null. Ive used the following:

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

I get the below result

enter image description here

Is there a way to get all the hours on one line for each pattern ID?

Thank you in advance!

Solution

  • Try to use 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

    And after that you can replace labels

    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