SQLTeam.com | Weblogs | Forums

Combine multiple records into a single row

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

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.

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

Many thanks for your help
Martyn

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