Come ruotare le colonne di testo in SQL Server?

Ho una tabella come questa nel mio 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 

Devo visualizzare le risorse, le persone, l’ambiente e la reputazione come colonne e visualizzare i valori di Desc come valori. Ma quando eseguo la query pivot, tutti i miei valori sono nulli.

Qualcuno può esaminare la mia domanda e dirmi dove sto sbagliando?

 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 

Ecco la mia uscita

 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 

     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 

    Ho ricreato questo in SQL Server e funziona bene.

    Sto cercando di convertire questo in modo che funzioni quando non si sa quale sarà il contenuto nelle colonne TYPE e DESCRIPTION.

    Stavo anche usando questo come guida. ( Converti righe in colonne usando ‘Pivot’ in SQL Server )

    MODIFICARE —-

    Ecco la mia soluzione per quanto sopra in cui NON CONOSCETE il contenuto in entrambi i campi ….

     -- setup commands drop table #mytemp go create table #mytemp ( id varchar(10), Metal_01 varchar(30), Metal_02 varchar(100) ) -- insert the data insert into #mytemp select 'C-0','Metal One','Metal_One' union all select 'C-0','Metal & Two','Metal_Two' union all select 'C-1','Metal One','Metal_One' union all select 'C-1','Metal (Four)','Metal_Four' union all select 'C-2','Metal (Four)','Metal_Four' union all select 'C-2','Metal / Six','Metal_Six' union all select 'C-3','Metal Seven','Metal_Seven' union all select 'C-3','Metal Eight','Metal_Eight' -- prepare the data for rotating: drop table #mytemp_ReadyForRotate select *, replace( replace( replace( replace( replace( mt.Metal_01,space(1),'_' ) ,'(','_' ) ,')','_' ) ,'/','_' ) ,'&','_' ) as Metal_No_Spaces into #mytemp_ReadyForRotate from #mytemp mt select 'This is the content of "#mytemp_ReadyForRotate"' as mynote, * from #mytemp_ReadyForRotate -- this is for when you KNOW the content: -- in this query I am able to put the content that has the punctuation in the cell under the appropriate column header Select id, pt.Metal_One, Metal_Two, Metal_Four, Metal_Six, Metal_Seven,Metal_Eight FROM ( select * from #mytemp ) As Temp PIVOT ( max(Metal_01) FOR Metal_02 In( Metal_One, Metal_Two, Metal_Four, Metal_Six, Metal_Seven, Metal_Eight ) ) As pt -- this is for when you DON'T KNOW the content: -- in this query I am UNABLE to put the content that has the punctuation in the cell under the appropriate column header -- unknown as to why it gives me so much grief - just can't get it to work like the above -- it WORKS just fine but not with the punctuation field drop table ##csr_Metals_Rotated go DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @InsertIntoTempTable as nvarchar(4000) select @cols = STUFF((SELECT ',' + QUOTENAME(Metal_No_Spaces) from #mytemp_ReadyForRotate group by Metal_No_Spaces order by Metal_No_Spaces FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT id,' + @cols + ' into ##csr_Metals_Rotated from ( select id as id, Metal_No_Spaces from #mytemp_ReadyForRotate ) x pivot ( max(Metal_No_Spaces) for Metal_No_Spaces in (' + @cols + ') ) p ' execute(@query); select * from ##csr_Metals_Rotated