Question on Combining rows

# Question on Combining rows

• Tuesday, March 06, 2012 2:46 PM

Could someone help with the following.  I am drawing a blank on how I can do this.  I have the following return set

and I would like to combine all 'part numbers' that are in the format 'partnum' and 'partnum + PP'.

ie: part number:  TS133273C AND TS133273CPP - combined into TS133273C, val 2 = 28, val3 = 3 etc.

custid     groupname           part num          description                                         val1    val2             val3    val4             val5

12345    #1 GROUPNAME    TS133273C      RED ALE - WITH DARK ROAD GRAIN    0.00    3.00            1.00    0.00            403
12345    #1 GROUPNAME    TS133273CPP  RED ALE - WITH DARK ROAD GRAIN    0.00    25.00          2.00    23730.00    403
12345    #1 GROUPNAME    TR155578C      RED ALE - WITH DARK ROAD GRAIN    0.00    9000.00      0.00    16000.00    426
12345    #1 GROUPNAME    TR155578CPP  RED ALE - WITH DARK ROAD GRAIN    0.00    0.00            0.00    0.00            0

Here is the query that I am using.

SELECT  tbl1.custid ,
tbl1.groupname ,
tbl1.partnum ,
tbl1.descrip ,
val1 = ISNULL(SUM(CASE tbl2.whse
WHEN 'eop' THEN tbl2.qtyonhand
END), 0) ,
val2 = ISNULL(SUM(CASE tbl2.whse
WHEN 'eoo' THEN tbl2.qtyonhand
END), 0) ,
val3 = ISNULL(SUM(CASE tbl2.whse
WHEN 'epp' THEN tbl2.qtyonhand
END), 0) ,
val4 = ISNULL(SUM(CASE WHEN tbl2.whse LIKE '%/g' THEN tbl2.qtyonhand
END), 0) ,
val5 = ISNULL(SUM(CASE WHEN tbl2.whse LIKE '%/g'
THEN ( DATEDIFF(dd, tbl2.lastrcptdt, GETDATE()) )
END), 0)
FROM    tbl1
INNER JOIN tbl2 ON ( tbl1.partnum = tbl2.partnum )
AND ( tbl1.cono = tbl2.cono )
WHERE   tbl1.cono = 1
AND tbl1.custid = '12345'
AND tbl1.statustype = 'a'
GROUP BY tbl1.custid ,
tbl1.groupname ,
tbl1.partnum ,
tbl1.descrip

Thanks Dave.

### All Replies

• Tuesday, March 06, 2012 2:52 PM

Hi, try this:

```SELECT  tbl1.custid ,
tbl1.groupname,
CASE RIGHT(tbl1.partnum WHEN 'PP' THEN LEFT(tbl1.partnum,LEN(tbl1.partnum)-2) ELSE tbl1.partnum END AS partnum,
tbl1.descrip ,
val1 = ISNULL(SUM(CASE tbl2.whse
WHEN 'eop' THEN tbl2.qtyonhand
END), 0) ,
val2 = ISNULL(SUM(CASE tbl2.whse
WHEN 'eoo' THEN tbl2.qtyonhand
END), 0) ,
val3 = ISNULL(SUM(CASE tbl2.whse
WHEN 'epp' THEN tbl2.qtyonhand
END), 0) ,
val4 = ISNULL(SUM(CASE WHEN tbl2.whse LIKE '%/g' THEN tbl2.qtyonhand
END), 0) ,
val5 = ISNULL(SUM(CASE WHEN tbl2.whse LIKE '%/g'
THEN ( DATEDIFF(dd, tbl2.lastrcptdt, GETDATE()) )
END), 0)
FROM    tbl1
INNER JOIN tbl2 ON ( tbl1.partnum = tbl2.partnum )
AND ( tbl1.cono = tbl2.cono )
WHERE   tbl1.cono = 1
AND tbl1.user1 = '12345'
AND tbl1.statustype = 'a'
GROUP BY tbl1.custid ,
tbl1.groupname ,
CASE RIGHT(tbl1.partnum WHEN 'PP' THEN LEFT(tbl1.partnum,LEN(tbl1.partnum)-2) ELSE tbl1.partnum END ,
tbl1.descrip```

David.

• Edited by Tuesday, March 06, 2012 3:09 PM
•
• Tuesday, March 06, 2012 3:06 PM
Moderator

Try:

```DECLARE @Info TABLE (custid  INT,   groupname  VARCHAR(30),   [part num] VARCHAR(30),
description   VARCHAR(100),
val1  DECIMAL(10,2),
val2  DECIMAL(10,2),
val3   DECIMAL(10,2),
val4   DECIMAL(10,2),
val5 DECIMAL(10,2))

INSERT INTO @Info
SELECT
12345,    '#1 GROUPNAME',    'TS133273C',      'RED ALE - WITH DARK ROAD GRAIN',    0.00,    3.00,            1.00,    0.00,            403
UNION ALL SELECT
12345,    '#1 GROUPNAME',    'TS133273CPP',  'RED ALE - WITH DARK ROAD GRAIN',    0.00,    25.00,          2.00,    23730.00,    403
UNION ALL SELECT
12345,    '#1 GROUPNAME',    'TR155578C',      'RED ALE - WITH DARK ROAD GRAIN',    0.00,    9000.00,      0.00,    16000.00,    426
UNION ALL SELECT
12345,    '#1 GROUPNAME',    'TR155578CPP',  'RED ALE - WITH DARK ROAD GRAIN',    0.00,    0.00,            0.00,    0.00,            0

SELECT CustID, groupname, F.PartNum, i.description,
SUM(i.val1) AS Val1,
SUM(i.val2) AS Val2,
SUM(i.val3) AS Val3,
SUM(i.val4) AS Val4,
SUM(i.val5) AS Val5
FROM @Info I
CROSS APPLY (SELECT CASE WHEN I.[PART num] LIKE '%PP'
THEN LEFT(i.[part num],LEN(i.[part num])-2)  ELSE i.[part num] END AS PartNum) F
GROUP BY i.custid, i.groupname, F.PartNum, i.description   ```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Proposed As Answer by Tuesday, March 06, 2012 6:38 PM
• Marked As Answer by Tuesday, March 06, 2012 7:06 PM
•
• Tuesday, March 06, 2012 7:06 PM

Thank you very much for the help, that worked perfectly.

Naomi/dac03:  Quick question, when grouping using a temp table,  the columns group

in the correct order ie: all the rec's for a groupname with all the partnum's in

descending order.  But when I use a Common Table Expression (CTE) the grouping

is funky.  ie: the groupname, partnum are not together.  is that because when you

use a CTE you don't define the types for the columns.

Dave

• Marked As Answer by Tuesday, March 06, 2012 7:06 PM
• Unmarked As Answer by Tuesday, March 06, 2012 7:06 PM
•
• Tuesday, March 06, 2012 7:18 PM
Moderator

Without using an ORDER BY there is never 100% guarantee of the order of the rows.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog