PIVOT in SQL 2005 problem
-
I'm trying to use PIVOT instead of CASE but can't make it, what's wrong with the pivot query below? create table t1 (a int, b char(5)) go insert t1 values(1, 'SVE') insert t1 values(1, 'FIN') insert t1 values(1, 'DAN') insert t1 values(2, 'SVE') insert t1 values(2, 'FIN') insert t1 values(3, 'SVE') select * from t1 a b ----------- ----- 1 SVE 1 FIN 1 DAN 2 SVE 2 FIN 3 SVE (6 row(s) affected) -- SQL 2000 with case works select a, max(case when b='SVE' then b else '' end) as b_sve, max(case when b='FIN' then b else '' end) as b_fin, max(case when b='DAN' then b else '' end) as b_dan from t1 group by a a b_sve b_fin b_dan ----------- ----- ----- ----- 1 SVE FIN DAN 2 SVE FIN 3 SVE (3 row(s) affected) -- SQL 2005 with pivot, this select doesn't work! select a, isnull(['SVE'], '') as b_sve, isnull(['FIN'], '') as b_fin, isnull(['DAN'], '') as b_dan from (select a, b from t1) p pivot ( max(b) for b in ( ['SVE'], ['FIN'], ['DAN'] ) ) pvt; a b_sve b_fin b_dan ----------- ----- ----- ----- 1 2 3 (3 row(s) affected)
Håkan Nilsson -- Swedish developer, DBA, C#, SQL Server, soccer trainer, old C64 player