Flattening variable length DB strings into one column
I have searched and cannot find a solution for this that suits my needs exactly and cannot find one that I can change. 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 media that processed the package. I can make a request like
SELECT packageID, carrier
FROM packageFlow
ORDER BY sequence
to get a list of all the people who handled the packets that look like this:
packageID, carrier 1, Bob 1, Jim 1, Sally 1, Ron 2, Reggie 2, Mary 2, Bruce
However, I need to get results in lines that look like this:
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 am not collecting anything and I cannot get the CTE to work correctly. I would appreciate any nudges in the right direction.
source to share
This is data transformation PIVOT
. Starting in SQL Server 2005, there is a function that converts rows to columns.
If you have a known number of values, you can program 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 values Carrier
that you want to include in columns, 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 replace order by packageid
withorder 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) |
source to share