How to pivot text columns in SQL Server?
pivot, sql, sql-server-2008
Solution
Select severity_id, pt.People, Assets, Environment, Reputation
FROM
(
select * from COMM.Consequence
) As Temp
PIVOT
(
max([DESCRIPTION])
FOR [TYPE] In([People], [Assets], [Environment], [Reputation])
) As pt
Problem
I have a table like this in my database (SQL Server 2008) ``` ID Type Desc -------------------------------- C-0 Assets No damage C-0 Environment No impact C-0 People No injury or health effect C-0 Reputation No impact C-1 Assets Slight damage C-1 Environment Slight environmental damage C-1 People First Aid Case (FAC) C-1 Reputation Slight impact; Compaints from local community ``` i have to display the Assets, People, Environment and Reputation as columns and display matched Desc as values. But when i run the pivot query, all my values are null. Can somebody look into my query ans tell me where i am doing wrong? ``` Select severity_id,pt.[1] As People, [2] as Assets , [3] as Env, [4] as Rep FROM ( select * from COMM.Consequence ) As Temp PIVOT ( max([DESCRIPTION]) FOR [TYPE] In([1], [2], [3], [4]) ) As pt ``` Here is my output ``` ID People Assets Env Rep ----------------------------------- C-0 NULL NULL NULL NULL C-1 NULL NULL NULL NULL C-2 NULL NULL NULL NULL C-3 NULL NULL NULL NULL C-4 NULL NULL NULL NULL C-5 NULL NULL NULL NULL ```