[SQL] 어떻게 SQL 서버의 텍스트 열을 피벗?

어떻게 SQL 서버의 텍스트 열을 피벗?

내 데이터베이스에이 같은 테이블이 (SQL 서버 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

난 열과 자산, 사람, 환경, 평판 표시하고, 값으로 소계 유사한 표시하도록. 내가 피벗 쿼리를 실행할 때, 내 모든 값은 null입니다.

내 질문에 캔 누군가의 모양과 내가 잘못하고있는 중이 야 어디 있는지 말해?

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

여기 내 출력은

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

해결법

1. ==============================

   1.

   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
   

2. ==============================

   2.나는 SQL 서버에서이 문제를 재현하고 그것을 잘 작동합니다.

   나는 SQL 서버에서이 문제를 재현하고 그것을 잘 작동합니다.

   나는 한 내용이 TYPE 및 설명 컬럼에있을 것입니다 알고하지 않은 경우 작업이 변환 할 노력하고있어.

   또한 가이드로 이것을 사용하고 있었다. (SQL Server의 '피봇'를 사용하여 열을 행으로 변환)

   편집하다 ----

   다음은 두 필드의 내용을 모르는 경우 위의 내 솔루션은 ....입니다

   -- 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
   

from https://stackoverflow.com/questions/10025934/how-to-pivot-text-columns-in-sql-server by cc-by-sa and MIT license