패턴이므로, 갑자기 멍해질때 붙여넣기 하기위해 저장해둠
--특정항목 연결된 5개를 가로로 표시
--데이터 만드는 부분
SELECT
ZT.DeptCd, ZT.UserId, ZT.ROW_Rank
INTO #T_A
FROM
(
SELECT
DP.DeptCd, 'Z_' + LEFT(DU.UserId,3) AS UserId
, ROW_NUMBER() OVER(PARTITION BY DP.DeptCd ORDER BY DP.DeptCd) AS ROW_Rank
FROM ZZ_Dept AS DP
INNER JOIN ZZ_DeptUser AS DU
ON DP.DeptCd = DU.DeptCd
WHERE DP.DeptCd LIKE 'Z%'
) AS ZT
WHERE ZT.ROW_Rank < 6
--피벗적용
SELECT
DeptCd,[1],[2],[3],[4],[5]
FROM
(
SELECT * FROM #T_A
) AS PVT
PIVOT
(
MAX(UserId)
FOR ROW_Rank IN ([1],[2],[3],[4],[5])
) AS P
--결과
DeptCd 1 2 3 4 5
Z0 Z_050 Z_900 NULL NULL NULL
Z1 Z_008 Z_010 Z_085 Z_086 Z_086
Z2 Z_003 Z_004 Z_004 Z_050 Z_050
Z4 Z_011 Z_026 Z_050 Z_051 Z_052
Z7 Z_003 Z_004 Z_011 Z_011 Z_011
Z8 Z_006 NULL NULL NULL NULL
Z9 Z_051 Z_052 Z_053 NULL NULL
