mssql 행을 열로 변경하기 pivot 사용함

https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask/

create table ttt

( 

idx [bigint] IDENTITY(1,1) NOT NULL,

userid bigint null,

groupid int null,

subjects nvarchar(50) null,

contents nvarchar(max)  null,

);

insert into ttt 

select 1, 1, N'11동해물과', N'11동해물과_내용' union all

select 1, 2, N'12백두산이', N'12동해물과_내용' union all

select 1, 3, N'13마르고 닳도록', N'13동해물과_내용' union all

select 1, 4, N'14하느님이 보우하사', N'14동해물과_내용' union all

select 1, 5, N'15우리나라만세', N'15동해물과_내용'  union all

select 2, 1, N'21동해물과', N'21동해물과_내용' union all

select 2, 2, N'22백두산이', N'22동해물과_내용' union all

select 3, 1, N'31마르고 닳도록', N'31동해물과_내용' union all

select 3, 2, N'32하느님이 보우하사', N'32동해물과_내용' union all

select 3, 3, N'33우리나라만세', N'33동해물과_내용';

WITH

with_subjects AS

(

SELECT userid, [1] '1제목', [2]  '2제목', [3]  '3제목', [4]  '4제목', [5]  '5제목' FROM (     

SELECT userid, groupid, subjects    

FROM ttt WITH (NOLOCK) 

) AS A 

PIVOT (     

MIN(subjects)

FOR groupid IN ([1], [2], [3], [4], [5]) 

) A

),

with_contents AS

(

SELECT userid, [1] '1내용', [2]  '2내용', [3]  '3내용', [4]  '4내용', [5]  '5내용' FROM (     

SELECT userid, groupid, contents    

FROM ttt WITH (NOLOCK) 

) AS A 

PIVOT (     

MIN(contents)

FOR groupid IN ([1], [2], [3], [4], [5]) 

) A

)

SELECT *

FROM with_subjects AS s 

  INNER JOIN with_contents AS c

  ON s.userid = c.userid;