Combine multiple records into a single row

Transact-SQL
1

I have a query requirement where I need to roll up some data from multiple rows into a single row. All the rows involved are in the same table, the rows to be rolled up are linked to the parent item row by means of a record id in a field called PARITEM which links to the field CIRECID on the main item row.

I only need to combine the value fields CHARGE, VAT & LINETOT, all other details in the parent row should remain the same (there are many other columns in this table - I have pared it down for this question) and the other rows should not be shown.

Here is the basic query:

SELECT ITEMNO
	,ITEMDESC#1
	,CIRECID
	,PARITEM
	,CHARGE
	,VAT
	,LINETOT
FROM INVITEMS

Which returns this as an example:

+------------+-------------------------+----------------------------------+----------------------------------+------------+------+---------+
|   ITEMNO   |       ITEMDESC#1        |             CIRECID              |             PARITEM              |   CHARGE   | VAT  | LINETOT |
+------------+-------------------------+----------------------------------+----------------------------------+------------+------+---------+
| 05EX0189   | KUBOTA U48 5T EXCAVATOR | B325080EDACA4C86B654DD8759961318 |                                  |265.000000  |53.00 | 318.00  |
| GRADING    | Grading Bucket          | AD4408FA20C7490BAAD61B0CF287FF5E | B325080EDACA4C86B654DD8759961318 | 10.000000  | 2.00 |  12.00  |
| TLBUCKET18 | Toothless Bucket 18"    | 556E83BFE13D45E4B8E33331A52A596D | B325080EDACA4C86B654DD8759961318 | 10.000000  | 2.00 |  12.00  |
| TLBUCKET24 | Toothless Bucket 24"    | B6DFC787D4E0453BB1F00BB3069F2B66 | B325080EDACA4C86B654DD8759961318 | 10.000000  | 2.00 |  12.00  |
| TLBUCKET30 | Toothless Bucket 30"    | 76E2DA521BE84E5D880E371DED8F3184 | B325080EDACA4C86B654DD8759961318 | 10.000000  | 2.00 |  12.00  |
+------------+-------------------------+----------------------------------+----------------------------------+------------+------+---------+

However, what I want to see is this:

+----------+-------------------------+----------------------------------+---------+------------+------+---------+
|  ITEMNO  |       ITEMDESC#1        |             CIRECID              | PARITEM |   CHARGE   | VAT  | LINETOT |
+----------+-------------------------+----------------------------------+---------+------------+------+---------+
| 05EX0189 | KUBOTA U48 5T EXCAVATOR | B325080EDACA4C86B654DD8759961318 |         | 305.000000 |61.00 | 366.00  |
+----------+-------------------------+----------------------------------+---------+------------+------+---------+

How can I accomplish this? A create table statement and the sample data is below:

CREATE TABLE [INVITEMS] 
	(
	[ITEMNO] [varchar](20) NULL,
	[ITEMDESC#1] [varchar](60) NULL,
	[CIRECID] [varchar](32) NULL,
	[PARITEM] [varchar](32) NULL,
	[CHARGE] [decimal](15,6) NULL,
	[VAT] [decimal](15,2) NULL,
	[LINETOT] [decimal](15, 2) NULL
	)

INSERT INTO INVITEMS ([ITEMNO],[ITEMDESC#1],[CIRECID],[PARITEM],[CHARGE],[VAT],[LINETOT]) VALUES ('05EX0189','KUBOTA U48 5T EXCAVATOR','B325080EDACA4C86B654DD8759961318','','265','0','265')
INSERT INTO INVITEMS ([ITEMNO],[ITEMDESC#1],[CIRECID],[PARITEM],[CHARGE],[VAT],[LINETOT]) VALUES ('GRADING','Grading Bucket','AD4408FA20C7490BAAD61B0CF287FF5E','B325080EDACA4C86B654DD8759961318','0','0','0')
INSERT INTO INVITEMS ([ITEMNO],[ITEMDESC#1],[CIRECID],[PARITEM],[CHARGE],[VAT],[LINETOT]) VALUES ('TLBUCKET18','Toothless Bucket 18','556E83BFE13D45E4B8E33331A52A596D','B325080EDACA4C86B654DD8759961318','0','0','0')
INSERT INTO INVITEMS ([ITEMNO],[ITEMDESC#1],[CIRECID],[PARITEM],[CHARGE],[VAT],[LINETOT]) VALUES ('TLBUCKET24','Toothless Bucket 24','B6DFC787D4E0453BB1F00BB3069F2B66','B325080EDACA4C86B654DD8759961318','0','0','0')
INSERT INTO INVITEMS ([ITEMNO],[ITEMDESC#1],[CIRECID],[PARITEM],[CHARGE],[VAT],[LINETOT]) VALUES ('TLBUCKET30','Toothless Bucket 30','76E2DA521BE84E5D880E371DED8F3184','B325080EDACA4C86B654DD8759961318','0','0','0')

EDIT: I should have mentioned that there is also a field called SAFEFLAG in this table which shows whether a line is a parent or child. 0 means it is a parent item, 1 means it is a child.

Many thanks in advance for any help with this.

Martyn

2

The INSERT script had some errors.

INSERT INTO INVITEMS ([ITEMNO],[ITEMDESC#1],[CIRECID],[PARITEM],[CHARGE],[VAT],[LINETOT]) VALUES 
('05EX0189','KUBOTA U48 5T EXCAVATOR','B325080EDACA4C86B654DD8759961318','',265,53,318),
('GRADING','Grading Bucket','AD4408FA20C7490BAAD61B0CF287FF5E','B325080EDACA4C86B654DD8759961318',10,2,12),
('TLBUCKET18','Toothless Bucket 18','556E83BFE13D45E4B8E33331A52A596D','B325080EDACA4C86B654DD8759961318',10,2,12),
('TLBUCKET24','Toothless Bucket 24','B6DFC787D4E0453BB1F00BB3069F2B66','B325080EDACA4C86B654DD8759961318',10,2,12),
('TLBUCKET30','Toothless Bucket 30','76E2DA521BE84E5D880E371DED8F3184','B325080EDACA4C86B654DD8759961318',10,2,12)

Give this a try:

; WITH CTE_parent AS (
SELECT *
FROM INVITEMS P
WHERE P.PARITEM = ''
)
, CTE_parts AS (
SELECT I.PARITEM, SUM(I.CHARGE) as Charge, SUM(I.VAT) as Vat, SUM(I.LINETOT) as LineTot
FROM INVITEMS I
WHERE I.PARITEM <> ''
GROUP BY I.PARITEM
)
SELECT P.ITEMNO, P.ITEMDESC#1, P.CIRECID, P.PARITEM
	, P.CHARGE + COALESCE(I.Charge, 0) as CHARGE
	, P.VAT + COALESCE(I.Vat, 0) as VAT
	, P.LINETOT + COALESCE(I.LineTot, 0) as LINETOT
FROM CTE_parent as P
	LEFT OUTER JOIN CTE_parts as I
		On P.CIRECID = I.PARITEM

The first CTE extracts the parent items. You should use the SAFEFLAG for that.

3

Apologies for the errors, I've tried this and it works as desired.

Many thanks for your help
Martyn

4

No apologies needed, just wanted to inform you about the sample data.
Glad it was helpful.