Flatten variable length DB rows into a single column

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.

Solution

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) |