동적 pivot

동적 pivot 샘플 코드

DECLARE @col NVARCHAR(MAX) DECLARE @query NVARCHAR(MAX) DECLARE @StndCodeBizDvs NVARCHAR(10) DECLARE @StndItemCode NVARCHAR(30) SET @StndCodeBizDvs = 'M' SET @StndItemCode ='3439084' SET @col = '' SELECT @col = @col +'[' + AA.LoborCode + '],' FROM ( SELECT DISTINCT A.LoborCode , B.ItemSize FROM dbo.DDETB_Z1_StndLaborQty A --표준공량 LEFT OUTER JOIN dbo.DDETB_Z1_StndItemList B ON B.StndCodeBizDvs = 'Z' AND ItemDvs = 'L' AND A.LoborCode = B.StndItemCode WHERE A.StndCodeBizDvs = 'M' AND A.BaseYYMM = '2016-06-01' AND A.StndItemCode LIKE '3439084%' ) AA SET @col = LEFT(@col, LEN(@col) -1) SELECT @col SET @query = ' SELECT StndCodeBizDvs , StndItemCode , ItemName , ItemSize , ItemUnit , BaseYYMM , ' + @col + ' FROM ( SELECT A.StndCodeBizDvs , A.StndItemCode , B.ItemName , B.ItemSize , B.ItemUnit , A.BaseYYMM , A.LoborCode , A.LaborQty FROM dbo.DDETB_Z1_StndLaborQty A LEFT OUTER JOIN dbo.DDETB_Z1_StndItemList B ON A.StndCodeBizDvs = B.StndCodeBizDvs AND A.StndItemCode = B.StndItemCode WHERE A.StndCodeBizDvs = '''+@StndCodeBizDvs+''' AND A.StndItemCode LIKE '''+@StndItemCode+'%'' ) A PIVOT ( MAX(LaborQty) FOR LoborCode IN ('+@col+') ) P ' exec sp_executesql @query;

샘플 데이터