Flatten variable length DB rows into a single column

Question

I've searched and can't find a solution to this that exactly fits my needs, nor can I find one that I can modify. I have a database table, for simplicity we'll say it has three columns (packageID, carrier, and sequence). For any package there can be one or more carriers that have handled the package. I can do a query like

SELECT packageID, carrier 
FROM packageFlow 
ORDER BY sequence

to get a list of all the people that have handled packages that looks like:

packageID, carrier
1, Bob
1, Jim
1, Sally
1, Ron
2, Reggie
2, Mary
2, Bruce

What I need though is to get the results into rows that look like:

packageID|carrier1|carrier2|carrier3|carrier4
   1     |Bob     |Jim     |Sally   |Ron
   2     |Reggie  |Mary    |Bruce

Pivot doesn't seem to do what I need since I'm not aggregating anything and I can't get a CTE to work correctly either. I'd appreciate any nudges in the right direction.

You should tell us what RDBMS you're using. Oracle `LEAD` helps with this kind of thing. — Marc, Mar 01 '13 at 16:31
duplicate http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings ? — mdn, Mar 01 '13 at 16:32
You've not specified a DBMS but [this answer](http://stackoverflow.com/questions/10791247/sql-server-possible-pivot-solution/10796492#10796492) has solutions for most DMBS. — GarethD, Mar 01 '13 at 16:48
Do you want the data concatenated into a single column? Or do you want separate columns? — Taryn, Mar 01 '13 at 17:03
Both of the above referenced posts are for concatenating them into a single column, which I already know how to do. I need multiple columns. I updated my post to be more clear. — dsvick, Mar 01 '13 at 17:03
It does look like a pivot, but you are pivoting on the row number on a partition of each package id. — Dan Metheus, Mar 01 '13 at 17:08

Taryn · Accepted Answer · 2013-03-01T17:17:00.923

This data transformation is a PIVOT. Starting in SQL Server 2005, there is a function that will convert the rows into columns.

If you have a known number of values, then you can hard-code your query:

select *
from 
(
  select packageid, carrier,
    'Carrier_'+cast(row_number() over(partition by packageid order by packageid) as varchar(10)) col
  from packageflow
) src
pivot
(
  max(carrier)
  for col in (Carrier_1, Carrier_2, Carrier_3, Carrier_4)
) piv

See SQL Fiddle with Demo.

If you have an unknown number of Carrier values that you want to turn into columns, then you can use dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.col) 
                    from 
                    (
                      select 'Carrier_'+cast(row_number() over(partition by packageid order by packageid) as varchar(10)) col
                      from packageFlow
                    ) t                    
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT packageid,' + @cols + ' from 
             (
                select packageid, carrier,
                  ''Carrier_''+cast(row_number() over(partition by packageid order by packageid) as varchar(10)) col
                from packageflow
            ) x
            pivot 
            (
                max(carrier)
                for col in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo.

Note: you will replace the order by packageid with order by sequence

The result of both queries is:

| PACKAGEID | CARRIER_1 | CARRIER_2 | CARRIER_3 | CARRIER_4 |
-------------------------------------------------------------
|         1 |       Bob |       Jim |     Sally |       Ron |
|         2 |    Reggie |      Mary |     Bruce |    (null) |

That's awesome!! Thanks, I got too wrapped up in the aggregate part of using a pivot table and couldn't get past it. — dsvick, Mar 01 '13 at 17:43
@dsvick I am glad that you got it working, always happy to help! :) — Taryn, Mar 01 '13 at 17:45

Flatten variable length DB rows into a single column

1 Answers1