unpivot the table for single column-CodePudding

I am trying to unpivot the table in sql but it's throwing an error. Help is appreciated

create table #test (id int ,GroupType varchar(50),State_NM varchar(20))

Insert Into #test values (100,  'Facility'  ,'TX')
Insert Into #test values (100,  'Group'     ,NULL)
Insert Into #test values (100,  'JobTitle'  ,'TX')
Insert Into #test values (100,  'JobTitle'  ,'NC')
Insert Into #test values (101,  'Facility'  ,'NM')
Insert Into #test values (101,  'Group'     ,'TX')
Insert Into #test values (102,  'Facility'  ,'TX')
Insert Into #test values (102,  'Group'     ,Null)
Insert Into #test values (102,  'Jobtitle'  ,'CA')

select * from #test

SELECT ID, facility,Group,JobTitle FROM   
(SELECT ID,GroupType,State_NM FROM #test )Tab1  
PIVOT  
(  
State_NM FOR GroupType IN ([facility],[Group[,[JobTitle])) AS Tab2  
ORDER BY Tab2.Name

Expected Result

CodePudding user response：

with string_agg() to "feed" your pivot

Example or dbFiddle

Select *
 From (
        select id
              ,GroupType
              ,Value = string_agg(state_NM,',')
         from #test
         Group by id,groupType
     ) src
 Pivot ( max(value) for GroupType IN ([facility],[Group],[JobTitle])) pvt

Results

id  facility    Group   JobTitle
100 TX          NULL    TX,NC
101 NM          TX      NULL
102 TX          NULL    CA

UPDATE: <=2016 Version

Select *
 From (
        select id
              ,GroupType
              ,Value = stuff( (Select ','  state_NM From #test Where ID=A.ID and GroupType=A.GroupType For XML Path ('')),1,1,'') 
         From #test A
         Group By id,GroupType
     ) src
 Pivot ( max(value) for GroupType IN ([facility],[Group],[JobTitle])) pvt