In order to get this result, you will have to UNPIVOT and hen PIVOT the data. The UNPIVOT will take the values in the columns fwildcardId, refNumber, wildcardName, wildcardValue and comments and turns them into rows. Once the data is in rows, then you can apply the PIVOT function to get the final result.
To unpivot the data, you can use either the UNPIVOT function or you can use the CROSS APPLY and VALUES clause.
UNPIVOT:
select recordid,
col+cast(rn as varchar(10)) col,
unpiv_value
from
(
select recordid,
cast(fwildcardid as varchar(10)) fwildcardid,
cast(refnumber as varchar(10)) refnumber,
cast(wildcardname as varchar(10)) name,
cast(wildcardvalue as varchar(10)) value,
cast(comments as varchar(10)) comments,
row_number() over(partition by recordid
order by fwildcardid) rn
from tempp
) d
unpivot
(
unpiv_value
for col in (fwildcardid, refnumber, name, value, comments)
) c
See SQL Fiddle with Demo.
CROSS APPLY and VALUES:
select recordid,
col+cast(rn as varchar(10)) col,
value
from
(
select recordid,
cast(fwildcardid as varchar(10)) fwildcardid,
cast(refnumber as varchar(10)) refnumber,
wildcardname,
wildcardvalue,
comments,
row_number() over(partition by recordid
order by fwildcardid) rn
from tempp
) d
cross apply
(
values
('fwildcardid', fwildcardid),
('refnumber', refnumber),
('name', wildcardname),
('value', wildcardvalue),
('comments', comments)
) c (col, value)
See SQL Fiddle with Demo.
These convert the results in a format:
| RECORDID | COL | VALUE |
------------------------------------
| 404450 | fwildcardid1 | 154833 |
| 404450 | refnumber1 | 1 |
| 404450 | name1 | aa |
| 404450 | value1 | oi |
| 404450 | comments1 | p |
| 404450 | fwildcardid2 | 154834 |
When you unpivot data into the same column, it has to be the same datatype. You will notice that I applied a cast to the columns so the datatype is the same.
Once the data is in the row format, you can convert it back into columns with PIVOT:
select *
from
(
select recordid,
col+cast(rn as varchar(10)) col,
unpiv_value
from
(
select recordid,
cast(fwildcardid as varchar(10)) fwildcardid,
cast(refnumber as varchar(10)) refnumber,
cast(wildcardname as varchar(10)) name,
cast(wildcardvalue as varchar(10)) value,
cast(comments as varchar(10)) comments,
row_number() over(partition by recordid
order by fwildcardid) rn
from tempp
) d
unpivot
(
unpiv_value
for col in (fwildcardid, refnumber, name, value, comments)
) c
) src
pivot
(
max(unpiv_value)
for col in (fwildcardid1, refnumber1, name1, value1, comments1,
fwildcardid2, refnumber2, name2, value2, comments2)
) piv;
See SQL Fiddle with Demo.
The above version works great if you have a known number of columns, but if you will have an unknown number of values that will be converted into columns, then you will need to use dynamic sql to get the result:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10)))
from
(
select row_number() over(partition by recordid
order by fwildcardid) rn
from tempp
) t
cross apply
(
select 'fwildcardid' col, 1 sortorder union all
select 'refNumber', 2 union all
select 'name', 3 union all
select 'value', 4 union all
select 'comments', 5
) c
group by col, rn, sortorder
order by rn, sortorder
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT recordid,' + @cols + ' from
(
select recordid,
col+cast(rn as varchar(10)) col,
unpiv_value
from
(
select recordid,
cast(fwildcardid as varchar(10)) fwildcardid,
cast(refnumber as varchar(10)) refnumber,
cast(wildcardname as varchar(10)) name,
cast(wildcardvalue as varchar(10)) value,
cast(comments as varchar(10)) comments,
row_number() over(partition by recordid
order by fwildcardid) rn
from tempp
) d
unpivot
(
unpiv_value
for col in (fwildcardid, refnumber, name, value, comments)
) c
) src
pivot
(
max(unpiv_value)
for col in (' + @cols + ')
) p '
execute(@query);
See SQL Fiddle with Demo. Both of these give the result:
| RECORDID | FWILDCARDID1 | REFNUMBER1 | NAME1 | VALUE1 | COMMENTS1 | FWILDCARDID2 | REFNUMBER2 | NAME2 | VALUE2 | COMMENTS2 |
-------------------------------------------------------------------------------------------------------------------------------
| 404450 | 154833 | 1 | aa | oi | p | 154834 | 2 | aaa | p | p |
| 406115 | 154867 | 1 | 98 | ff | ff | (null) | (null) | (null) | (null) | (null) |
| 406199 | 154869 | 1 | kki | aaaa | ssss | (null) | (null) | (null) | (null) | (null) |
| 406212 | 154880 | 1 | bbbbb | card | comm | (null) | (null) | (null) | (null) | (null) |
