Pivot data to a set number of columns per invoice

Transact-SQL
1

I have a query that lists the timesheets attached to an invoice. Currently, there is one line per timesheet, but what I need is one line per invoice number, with the timesheet filenames in columns as TS#1, TS#2 etc. up to a maximum of 5 columns. If any invoice has less than 5 timesheets the remaining columns should be blank or null. How can I do this?

This is my query:

SELECT ih.INVNO, SUBSTRING (lk.RECIPIENTEMAIL,0,CHARINDEX(';',lk.RECIPIENTEMAIL)) EMAIL,  ih.CONTNO + '-' + ts.TSNO + '.pdf' [TIMESHEET]
FROM INVHDR ih
LEFT JOIN CONTRACTS ct ON ct.CONTNO = ih.CONTNO
LEFT JOIN TIMESHEETS ts ON ts.INVNO = ih.INVNO
LEFT JOIN Lookup lk ON lk.ACCT = ih.ACCT
WHERE ct.INVRCODE = 'OPS1' AND ih.ACCT = '105371'

This gives the following results example:

+--------+--------------------+-------------------------------+
| INVNO  |       EMAIL        |           TIMESHEET           |
+--------+--------------------+-------------------------------+
| 843294 | accounts@tdwn1.com | 0000424820-WKEND0808.pdf      |
| 843294 | accounts@tdwn1.com | 0000424820-WKEND 15.08.21.pdf |
| 843294 | accounts@tdwn1.com | 0000424820-WKEND 22.08.21.pdf |
| 843303 | accounts@tdwn1.com | 0000438329-WKEND 22.08.21.pdf |
| 843294 | accounts@tdwn1.com | 0000424820-WKEND 05.09.21.pdf |
| 843303 | accounts@tdwn1.com | 0000438329-WKEND 05.09.21.pdf |
| 843303 | accounts@tdwn1.com | 0000438329-WKEND 29.08.21.pdf |
+--------+--------------------+-------------------------------+

But this is what I need to return:

+--------+--------------------+-------------------------------+-------------------------------+-------------------------------+------+------+
| INVNO  |       EMAIL        |             TS#1              |             TS#2              |             TS#3              | TS#4 | TS#5 |
+--------+--------------------+-------------------------------+-------------------------------+-------------------------------+------+------+
| 843294 | accounts@tdwn1.com | 0000424820-WKEND0808.pdf      | 0000424820-WKEND 15.08.21.pdf | 0000424820-WKEND 22.08.21.pdf |      |      |
| 843303 | accounts@tdwn1.com | 0000438329-WKEND 22.08.21.pdf |                               |                               |      |      |
| 843294 | accounts@tdwn1.com | 0000424820-WKEND 05.09.21.pdf |                               |                               |      |      |
| 843303 | accounts@tdwn1.com | 0000438329-WKEND 05.09.21.pdf | 0000438329-WKEND 29.08.21.pdf |                               |      |      |
+--------+--------------------+-------------------------------+-------------------------------+-------------------------------+------+------+

How can I do this? Not sure if PIVOT if the right solution - I couldn't seem to get what I wanted. Any help would be gratefully received.

Many thanks
Martyn

2 
;WITH cte_ih AS (
    SELECT ih.INVNO, ih.CONTNO + '-' + ts.TSNO + '.pdf' AS TIMESHEET,
        ROW_NUMBER() OVER(PARTITION BY ih.INVNO ORDER BY ih.CONTNO + '-' + ts.TSNO + '.pdf') AS TIMESHEET_num
    FROM INVHDR ih
    LEFT JOIN CONTRACTS ct ON ct.CONTNO = ih.CONTNO
    LEFT JOIN TIMESHEETS ts ON ts.INVNO = ih.INVNO
    WHERE ct.INVRCODE = 'OPS1' AND ih.ACCT = '105371'
)
SELECT ih.INVNO, SUBSTRING (lk.RECIPIENTEMAIL,0,CHARINDEX(';',lk.RECIPIENTEMAIL)) EMAIL,  
    MAX(CASE WHEN TIMESHEET_num = 1 THEN TIMESHEET ELSE NULL END) AS TS#1,
    MAX(CASE WHEN TIMESHEET_num = 2 THEN TIMESHEET ELSE NULL END) AS TS#2,
    MAX(CASE WHEN TIMESHEET_num = 3 THEN TIMESHEET ELSE NULL END) AS TS#3,
    MAX(CASE WHEN TIMESHEET_num = 4 THEN TIMESHEET ELSE NULL END) AS TS#4,
    MAX(CASE WHEN TIMESHEET_num = 5 THEN TIMESHEET ELSE NULL END) AS TS#5 /*, ...*/
FROM cte_ih AS ih
LEFT JOIN Lookup lk ON lk.ACCT = ih.ACCT
GROUP BY ih.INVNO, SUBSTRING (lk.RECIPIENTEMAIL,0,CHARINDEX(';',lk.RECIPIENTEMAIL))
ORDER BY ih.INVNO
2 Likes
3

Thanks Scott. I tested this and it works perfectly with what I am trying do.