Okay I have the following table.
Name ID Website
Aaron | 2305 | CoolSave1
Aaron | 8464 | DiscoWorld1
Adriana | 2956 | NewCin1
Adriana | 5991 | NewCin2
Adriana | 4563 NewCin3
I would like to transform it into the following way.
Adriana | 2956 | NewCin1 | 5991 | NewCin2 | 4563 | NewCin3
Aaron | 2305 | CoolSave1 | 8464 | DiscoWorld | NULL | NULL
As you can see i am trying to take the first name from the first table and make a single row with all the IDs / Websites associated with that name. The problem is, there is a variable amount of websites that may be associated with each name. To handle this i'd like to just make a table with with the number of fields sequal to the max line item, and then for the subsequent lineitems, plug in a NULL where there are not enough data.
In order to get the result, you will need to apply both the UNPIVOT and the PIVOT functions to the data. The UNPIVOT will take the columns (ID, website) and convert them to rows, once this is done, then you can PIVOT the data back into columns.
The UNPIVOT code will be similar to the following:
select name,
col+'_'+cast(col_num as varchar(10)) col,
value
from
(
select name,
cast(id as varchar(11)) id,
website,
row_number() over(partition by name order by id) col_num
from yt
) src
unpivot
(
value
for col in (id, website)
) unpiv;
See SQL Fiddle with Demo. This gives a result:
| NAME | COL | VALUE |
-------------------------------------
| Aaron | id_1 | 2305 |
| Aaron | website_1 | CoolSave1 |
| Aaron | id_2 | 8464 |
| Aaron | website_2 | DiscoWorld1 |
As you can see I applied a row_number() to the data prior to the unpivot, the row number is used to generate the new column names. The columns in the UNPIVOT must also be of the same datatype, I applied a cast to the id column in the subquery to convert the data to a varchar prior to the pivot.
The col values are then used in the PIVOT. Once the data has been unpivoted, you apply the PIVOT function:
select *
from
(
select name,
col+'_'+cast(col_num as varchar(10)) col,
value
from
(
select name,
cast(id as varchar(11)) id,
website,
row_number() over(partition by name order by id) col_num
from yt
) src
unpivot
(
value
for col in (id, website)
) unpiv
) d
pivot
(
max(value)
for col in (id_1, website_1, id_2, website_2, id_3, website_3)
) piv;
See SQL Fiddle with Demo.
The above version works great if you have a limited or known number of values. But if the number of rows is unknown, then you will need to use dynamic SQL to generate the result:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME( col+'_'+cast(col_num as varchar(10)))
from
(
select row_number() over(partition by name order by id) col_num
from yt
) t
cross apply
(
select 'id' col union all
select 'website'
) c
group by col, col_num
order by col_num, col
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT name,' + @cols + '
from
(
select name,
col+''_''+cast(col_num as varchar(10)) col,
value
from
(
select name,
cast(id as varchar(11)) id,
website,
row_number() over(partition by name order by id) col_num
from yt
) src
unpivot
(
value
for col in (id, website)
) unpiv
) x
pivot
(
max(value)
for col in (' + @cols + ')
) p '
execute(@query);
See SQL Fiddle with Demo. Both versions give the result:
| NAME | ID_1 | WEBSITE_1 | ID_2 | WEBSITE_2 | ID_3 | WEBSITE_3 |
------------------------------------------------------------------------
| Aaron | 2305 | CoolSave1 | 8464 | DiscoWorld1 | (null) | (null) |
| Adriana | 2956 | NewCin1 | 4563 | NewCin3 | 5991 | NewCin2 |