SQLTeam.com | Weblogs | Forums

GROUP BY / PIVOT - how to get row data to columns

I need to group some invoice data into a single row for each invoice. At the moment there could be up to 5 timesheet attachment rows per invoice, whereas I need these all on a single line.

This is what I get currently:

+-------+--------+------+--------------------+
| INVNO | ITEMNO | TSNO |      LOCATION      |
+-------+--------+------+--------------------+
|  1588 | E0424  | AZ1  | Z:\Timesheets\AZ1  |
|  1588 | E0424  | AZ2  | Z:\Timesheets\AZ2  |
|  1588 | E0424  | AZ3  | Z:\Timesheets\AZ3  |
|  1589 | M0394M | 1266 | Z:\Timesheets\1266 |
|  1589 | M0394M | 1267 | Z:\Timesheets\1267 |
+-------+--------+------+--------------------+

Whereas this is what I need, grouped by Invno and Itemno - remembering there could be up to 5 TS & location columns per row:

+-------+--------+-------+--------------------+-------+--------------------+-------+-------------------+
| INVNO | ITEMNO | TSNO1 |     LOCATION1      | TSNO2 |     LOCATION2      | TSNO3 |     LOCATION3     |
+-------+--------+-------+--------------------+-------+--------------------+-------+-------------------+
|  1588 | E0424  | AZ1   | Z:\Timesheets\AZ1  | AZ2   | Z:\Timesheets\AZ2  | AZ3   | Z:\Timesheets\AZ3 |
|  1589 | M0394M | 1266  | Z:\Timesheets\1266 | 1267  | Z:\Timesheets\1267 |       |                   |
+-------+--------+-------+--------------------+-------+--------------------+-------+-------------------+

Is this possible? My query is very simple so far:

SELECT ii.INVNO, ii.ITEMNO, ti.TSNO, ti.LOCATION
FROM INVITEMS ii
LEFT JOIN TIMESHEETS ti on ti.RECID = ii.TSREC

I have included a create table and the sample data below - any help with this would be appreciated:

CREATE TABLE INVITEMS (INVNO numeric(12,0), ITEMNO varchar(20), TSREC varchar(32))

CREATE TABLE TIMESHEETS (RECID varchar(32), TSNO varchar(20), LOCATION varchar(75))

INSERT INTO INVITEMS (INVNO, ITEMNO, TSREC) VALUES (1588,'E0424','DAF3237D2D9F42489D23DF7372950E52')
INSERT INTO INVITEMS (INVNO, ITEMNO, TSREC) VALUES (1588,'E0424','FAEFD41688554AE2A86A312B1A33A235')
INSERT INTO INVITEMS (INVNO, ITEMNO, TSREC) VALUES (1588,'E0424','2E1574C5E762424D98C9352846BEB812')
INSERT INTO INVITEMS (INVNO, ITEMNO, TSREC) VALUES (1589,'M0394M','41F71662377F4DEE9FAD1D6A4682855F')
INSERT INTO INVITEMS (INVNO, ITEMNO, TSREC) VALUES (1589,'M0394M','2B71111CED2D46D294BD285CBD2CDFE9')


INSERT INTO TIMESHEETS (RECID, TSNO, LOCATION) VALUES ('DAF3237D2D9F42489D23DF7372950E52','AZ1','Z:\TIMESHEETS\AZ1.pdf')
INSERT INTO TIMESHEETS (RECID, TSNO, LOCATION) VALUES ('FAEFD41688554AE2A86A312B1A33A235','AZ2','Z:\TIMESHEETS\AZ2.pdf')
INSERT INTO TIMESHEETS (RECID, TSNO, LOCATION) VALUES ('2E1574C5E762424D98C9352846BEB812','AZ3','Z:\TIMESHEETS\AZ3.pdf')
INSERT INTO TIMESHEETS (RECID, TSNO, LOCATION) VALUES ('41F71662377F4DEE9FAD1D6A4682855F','1266','Z:\TIMESHEETS\1266.pdf')
INSERT INTO TIMESHEETS (RECID, TSNO, LOCATION) VALUES ('2B71111CED2D46D294BD285CBD2CDFE9','1267','Z:\TIMESHEETS\1267.pdf')

Many thanks
Martyn

SELECT INVNO, ITEMNO,
    MAX(CASE WHEN row_num = 1 THEN TSNO ELSE '' END) AS TSNO1,
    MAX(CASE WHEN row_num = 1 THEN LOCATION ELSE '' END) AS LOCATION1,
    MAX(CASE WHEN row_num = 2 THEN TSNO ELSE '' END) AS TSNO2,
    MAX(CASE WHEN row_num = 2 THEN LOCATION ELSE '' END) AS LOCATION2,
    MAX(CASE WHEN row_num = 3 THEN TSNO ELSE '' END) AS TSNO3,
    MAX(CASE WHEN row_num = 3 THEN LOCATION ELSE '' END) AS LOCATION3,
    MAX(CASE WHEN row_num = 4 THEN TSNO ELSE '' END) AS TSNO4,
    MAX(CASE WHEN row_num = 4 THEN LOCATION ELSE '' END) AS LOCATION4,
    MAX(CASE WHEN row_num = 5 THEN TSNO ELSE '' END) AS TSNO5,
    MAX(CASE WHEN row_num = 5 THEN LOCATION ELSE '' END) AS LOCATION5
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ii.INVNO, ii.ITEMNO ORDER BY ti.TSNO) AS row_num    
    FROM INVITEMS ii
    LEFT JOIN TIMESHEETS ti on ti.RECID = ii.TSREC
) AS query1
GROUP BY INVNO, ITEMNO
ORDER BY INVNO, ITEMNO

Hi Scott

I am just curious

Is there any way to make this dynamic !!!
I mean we have max 3 but i see up to 5 ... hard coded !!

For Learning purpose !!!
In case I see something like this in future
I can book mark or save the solution !!!

Many thanks
:slight_smile:

Hi Scott

Thank you for this, it works well for my needs, I had completely forgotten about rownumber/over. There will never be more than 5 attachments required for this as they correspond to weeks.

Martyn

AnyBody

Could you please show

how to make it dynamic ??? !!
Thanks :slight_smile: