2

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

Andrew
  • 417
  • 5
  • 21

1 Answers1

1

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
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Thank you for the suggestion. I had thought something like that might work, but it doesn't. What I ended up with was:Chevy SUV Tahoe; Chevy Sedan Tahoe, Malibu, Impala; Ford SUV Escape, Explorer; Ford Sedan Escape, Explorer, Fusion, Taurus, Focus. It appears to be breaking on the Type, but when it's doing the connection, it's adding all of the results. – Andrew Nov 07 '16 at 14:54
  • That's odd because in the connect by I added type = prior type. so that it would only traverse the connection by make and type. thus only models for the same make/type should have been connected. – xQbert Nov 07 '16 at 15:02
  • Ahh, well, I missed your addition to the CONNECT BY line, which is why I had the issue I saw (sorry about that). I have now added that, but still have an issue. Now I am only getting 1 line for each Make. Chevy SUV Tahoe; Ford SUV Escape, Explorer. – Andrew Nov 07 '16 at 15:33
  • I must not understand the question, I thought that's the results you were after: Like makes/types combined on one line with model comma separated after for the same make/type. – xQbert Nov 07 '16 at 16:03
  • I think you do understand what I am looking for. However, my results are just 1 Type per Make. I was expecting Chevy SUV Tahoe; Chevy Sedan Malibu, Impala; Ford SUV Escape, Explorer; Ford Sedan Fusion, Focus, Taurus. But instead of each Type being listed, only 1 Type is being reported for each Make, with its appropriate Models. Chevy SUV Tahoe; Ford SUV Escape, Explorer. The other combinations Chevy Sedan Impala, Malibu; Ford Sedan Fusion, Focus, Taurus, are not returned. – Andrew Nov 07 '16 at 16:12
  • I'm not as familiar iwth the iSeries SQL vs others. So can you tell me if the iSeries supports ListAgg function or Pure XML as in this example? http://stackoverflow.com/questions/19098582/listagg-alternative-in-db2 ListAgg does exactly what you're looking for, as would the XMLAgg if neither of those two functions are available, then I begin to understand the row_number approach. – xQbert Nov 07 '16 at 16:29
  • without a db2 or iseries SQL this is challenging for me to brute force out the correct syntax. – xQbert Nov 07 '16 at 16:33
  • Thank you so much @xQbert. I found one other error I made when adapting your solution to my own tables. Once I corrected that error, I was able to get the results I was expecting, and was able to get the data to appear in the correct sequence. Thank you very much for your suggestions! – Andrew Nov 07 '16 at 18:55
  • 1
    Glad to be of service. If my solution didn't match what you ended up with I would recommend posting your final solution and accepting that as the answer and simply up voting mine as "aiding". I'd rather guide people to the correct activities if this helps someone in the future. – xQbert Nov 07 '16 at 19:00