I have data in a SQLServer table like this:
ID Name Year Value
-- ---- ---- -----
2 Ted 2013 2000
2 Ted 2012 1000
I need the view syntax to output this:
ID Name Yr1 Value1 Yr2 Value2
-- ---- --- ------ --- ------
2 Ted 2013 2000 2012 1000
No cursors if possible. Any clues would be greatful.
In SQL Server there are several ways that you can get the result.
If you have a limited number of values, then you can easily hard-code the result. One way you can get the result would be using an aggregate function with a CASE expression:
select d.id,
d.name,
max(case when seq = 1 then year end) year1,
max(case when seq = 1 then value end) value1,
max(case when seq = 2 then year end) year2,
max(case when seq = 2 then value end) value2
from
(
select id, name, year, value,
row_number() over(partition by id order by year desc) seq
from yourtable
) d
group by d.id, d.name;
See SQL Fiddle with Demo. If you want to use the PIVOT function, then I would suggest first unpivoting the data in the year and value columns first. The process of unpivot converts the multiple columns into multiple rows. You can use the UNPIVOT function, but in my example I used CROSS APPLY with a UNION ALL query and the code is:
select t.id, t.name,
col = c.col+cast(seq as varchar(4)),
c.val
from
(
select id, name, year, value,
row_number() over(partition by id order by year desc) seq
from yourtable
) t
cross apply
(
select 'year', t.year union all
select 'value', t.value
) c (col, val)
See SQL Fiddle with Demo. This converts your multiple columns into a slightly different format with multiple rows:
| ID | NAME | COL | VAL |
| 2 | Ted | year1 | 2013 |
| 2 | Ted | value1 | 2000 |
| 2 | Ted | year2 | 2012 |
| 2 | Ted | value2 | 1000 |
You can then apply the PIVOT function on this to get your final desired result:
select id, name, year1, value1, year2, value2
from
(
select t.id, t.name,
col = c.col+cast(seq as varchar(4)),
c.val
from
(
select id, name, year, value,
row_number() over(partition by id order by year desc) seq
from yourtable
) t
cross apply
(
select 'year', t.year union all
select 'value', t.value
) c (col, val)
) d
pivot
(
max(val)
for col in (year1, value1, year2, value2)
) piv;
See SQL Fiddle with Demo. Finally if you have an unknown number of values that you want to transform from rows into columns, then you can use dynamic SQL inside a stored procedure:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(4)))
from
(
select row_number() over(partition by id order by year desc) seq
from yourtable
) d
cross apply
(
select 'year', 1 union all
select 'value', 2
) c (col, so)
group by seq, col, so
order by seq, so
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, name,' + @cols + '
from
(
select t.id, t.name,
col = c.col+cast(seq as varchar(4)),
c.val
from
(
select id, name, year, value,
row_number() over(partition by id order by year desc) seq
from yourtable
) t
cross apply
(
select ''year'', t.year union all
select ''value'', t.value
) c (col, val)
) x
pivot
(
max(val)
for col in (' + @cols + ')
) p '
execute sp_executesql @query;
See SQL Fiddle with Demo. All versions will give a result:
| ID | NAME | YEAR1 | VALUE1 | YEAR2 | VALUE2 |
| 2 | Ted | 2013 | 2000 | 2012 | 1000 |
