TomBecker.net

Concatenating values across multiple rows

I had a hard time coming up with a relatively short name for the post, but here is what I wanted to get at. I have been struggling with this for quite some time now, and today I finally found a solution.

I have a base table that has a one-to-many relationship to a cross reference table. And what I wanted to do was create a single row per entry in the base base table with all of values from the cross ref table in a single column concatenated together.

So I looked at creating a pivot table, but that only got me so far. I could get all the different values that were in the cross ref table as columns, but that would give me multiple instances of my base record in the result set.

So, after much research and banging my head against the wall. I came across Tom Kyte's blog and an article called Stringing them Up.

Here is my version of his example. It has a little more in it than his example:

select A.BaseTable_ID,
      A.NAME,
      A.METHOD_DESC,
      A.DATA_DESC,
      A.REF_NO,
      B.TYPELIST

FROM   BaseTable A

INNER JOIN(
 select BaseTable_ID,
 max(sys_connect_by_path(type_desc, '/ ')) TYPELIST

 from (SELECT ID,
              type_desc,
              ROW_NUMBER() OVER PARTITION BY BaseTable_ID
               ORDER BY type_desc) rn
      from baseTable A
      inner join XRef B
      on A.TYPE_ID = b.type_id)

 start with rn =1
 connect by prior rn = rn-1
 and prior BaseTable_ID = BaseTable_ID
 group by BaseTable_ID
 order by BaseTable_ID ) B

ON A.BaseTable_ID = B.BaseTable_ID

Labels: Oracle

posted by Tom Becker at 2/20/2008