Question on Combining rows

Question

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.                    

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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