(MSSQL) 피벗 예제

/*
drop table #aaa
drop table #bbb
*/

CREATE Table #aaa
(
 KeyCol varchar(10),
 Section varchar(10)
)

CREATE Table #bbb
(
 KeyCol varchar(10),
 Name varchar(10),
 Score bigint
)

Insert Into #aaa SELECT 'A1001', 'Eng'
Insert Into #aaa SELECT 'A1002', 'Kor'
Insert Into #aaa SELECT 'A1003', 'Cha'

Insert Into #bbb SELECT 'A1001', '신근태', 90
Insert Into #bbb SELECT 'A1001', '우장일', 85
Insert Into #bbb SELECT 'A1001', '정종태', 80

Insert Into #bbb SELECT 'A1002', '신근태', 78
Insert Into #bbb SELECT 'A1002', '우장일', 88
Insert Into #bbb SELECT 'A1002', '정종태', 98

Insert Into #bbb SELECT 'A1003', '신근태', 85

--기본 Left Join
SELECT
 A.KeyCol, a.Section, b.Name, b.Score
FROM 
 #aaa a
 LEFT JOIN #bbb b ON a.KeyCol = b.KeyCol

--피벗
SELECT
 A.KeyCol, a.Section, b.*
FROM 
 #aaa a
 LEFT JOIN
 (
  SELECT 
   KeyCol,
   Cast([신근태] As varchar(10)) As [신근태],
   Cast([우장일] As varchar(10)) As [우장일],
   Cast([정종태] As varchar(10)) As [정종태]
  FROM
   #bbb
  PIVOT
  ( 
   MAX(Score) 
   FOR Name in ([신근태], [우장일], [정종태]) 
  ) as PivotTable 
 ) b ON a.KeyCol = b.KeyCol
 
 
--XML
SELECT
 A.KeyCol, a.Section, c.*
FROM 
 #aaa a
 LEFT JOIN
 (
  SELECT 
   Distinct KeyCol,
   
   STUFF((
   select ';' + cast(isnull(A.Name, '') as nvarchar(200))
   from
   (
    Select Distinct Name
    From #bbb
    Where KeyCol = B.KeyCol 
   ) A
   FOR XML PATH('')
   ),1,1,'') As Contents
  FROM
   #bbb B
 ) C ON a.KeyCol = c.KeyCol