PIVOT help, need PIVOT without aggregation

Question

Using the dml and sql below, the test table is created.  I am not clear on Pivot, can someone show how to get the following result:

id  2010  2009  2008  2007

1   1000  2000  3000  4000

2   5000  6000  7000  8000

create table test (
id int,
yr int,
hours int
)

insert into test select 1,2010,1000
insert into test select 1,2009,2000
insert into test select 1,2008,3000
insert into test select 1,2007,4000
insert into test select 2,2010,5000
insert into test select 2,2009,6000
insert into test select 2,2008,7000
insert into test select 2,2007,8000
go

select * from test

id          yr          hours
----------- ----------- -----------
1           2010        1000
1           2009        2000
1           2008        3000
1           2007        4000
2           2010        5000
2           2009        6000
2           2008        7000
2           2007        8000

Answer 1

Try

select * from Test PIVOT (SUM([hours]) FOR yr IN ([2010],[2009],[2008],[2007])) pvt 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

You need to use an aggregate function for pivot, but you can use, for example, max() to do what you want, so

create table test (
id int,
yr int,
hours int
)

insert into test select 1,2010,1000
insert into test select 1,2009,2000
insert into test select 1,2008,3000
insert into test select 1,2007,4000
insert into test select 2,2010,5000
insert into test select 2,2009,6000
insert into test select 2,2008,7000
insert into test select 2,2007,8000
go

select id, [2010], [2009], [2008], [2007]
from test
pivot
(max(hours) for yr in ([2010], [2009], [2008], [2007])) pvt

Thanks for the sample tables and data, that is always very helpful.

Tom

Answer 3

Thanks for the quick response...that works perfectly, but for a real-world situation, I won't know the actual year values, so is it possible to modify the IN clause somehow so it is dynamic?

Answer 4

Thanks for the quick response...that works perfectly, but for a real-world situation, I won't know the actual year values, so is it possible to modify the IN clause somehow so it is dynamic?

Answer 5

It is possible using dynamic pivot.

DECLARE @SQL NVARCHAR(MAX), @Cols NVARCHAR(MAX)

SELECT @Cols = STUFF((SELECT ', ' + QUOTENAME(yr) FROM (SELECT DISTINCT yr FROM Test) T
ORDER BY yr DESC FOR XML PATH('')),1,2,'')
SET @SQL = 
'select * from Test PIVOT (SUM([hours]) FOR yr IN (' +@Cols + ')) pvt'

EXECUTE (@SQL) 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Example for dynamic PIVOT:

http://www.sqlusa.com/bestpractices2008/dynamic-sql-pivot/

SSRS has built-in dynamic PIVOT. A real riot!

---

Kalman Toth SQL SERVER & BI TRAINING