Andrew Andrew - 1 month ago 10
SQL Question

Concatenate Multiple Row Values into 1 Row, with SQL for iSeries

First, I need to thank Kent Milligan and his article at http://www.mcpressonline.com/sql/techtip-combining-multiple-row-values-into-a-single-row-with-sql-in-db2-for-i.html for getting me as far in this problem as I have. But now I need to expand on what he has done here.

To avoid you having to go to his article, the problem he addressed was concatenating string data from multiple rows into a single row in the resulting table. For example:

Table Cars:


  • Make Model

  • Ford Fusion

  • Chevy Tahoe

  • Honda Odyssey

  • Ford Taurus

  • Ford Focus

  • Chevy Malibu



Results:


  • Make Model

  • Chevy Malibu, Tahoe

  • Ford Focus, Taurus, Fusion

  • Honda Odyssey



This was done with the SQL statement:

WITH numbered_sets(make, model, curr, prev) AS (
SELECT make, model,
ROW_NUMBER() OVER (PARTITION BY make ORDER BY model) AS curr,
ROW_NUMBER() OVER (PARTITION BY make ORDER BY model) -1 AS prev
FROM inventory)
SELECT make,
MAX (TRIM(L ',' FROM
CAST(SYS_CONNECT_BY_PATH(model, ',') AS VARCHAR(256)) ))
FROM numbered_sets
START WITH curr = 1
CONNECT BY make = PRIOR make AND prev = PRIOR curr
GROUP BY make


I was able to adapt that to my own table, and get most of the way where I wanted to get. But for my purposes, I have an additional column I need to include for the grouping. For example:

Table Cars:


  • Make Type Model

  • Ford Sedan Fusion

  • Chevy SUV Tahoe

  • Honda Minivan Odyssey

  • Ford Sedan Taurus

  • Ford Sedan Focus

  • Chevy Sedan Malibu

  • Ford SUV Escape

  • Ford SUV Explorer

  • Chevy Sedan Impala



For the Results, I’d be looking for:


  • Make Type Model

  • Chevy Sedan Malibu, Impala

  • Chevy SUV Tahoe

  • Ford Sedan Fusion, Taurus, Focus

  • Ford SUV Escape, Explorer

  • Honda Minivan Odyssey



Does anyone have any thoughts on what all I need to add to the original statement to be able to add the TYPE column, and GROUP on that accordingly? I’ve tried a handful of things, but I suspect I need to do something with the CONNECT_BY_PATH statement, I’m just not sure what.

Thank you

Answer

I think you just need to integrate type at the correct points thoughtout the query.

without being able to test, I think this would be close; but I may have missed something...

WITH numbered_sets(make, type, model, curr, prev) AS (
   SELECT make, type, model,
       ROW_NUMBER() OVER (PARTITION BY make, Type ORDER BY Make, Type, model) AS curr,
       ROW_NUMBER() OVER (PARTITION BY make, type ORDER BY Make, type, model) -1 AS prev
   FROM inventory)
SELECT make, Type
       MAX (TRIM(L ',' FROM
             CAST(SYS_CONNECT_BY_PATH(model, ',') AS VARCHAR(256)) ))
FROM numbered_sets
START WITH curr = 1
CONNECT BY make = PRIOR make AND prev = PRIOR curr and type = prior type
GROUP BY make, type

Perhaps we need do change the connect by to do a concat before connect by... though I can't see why this would help yet...

CONNECT BY concat(make,type) = PRIOR concat(make,type) AND prev = PRIOR curr