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