동적 pivot

동적 pivot 샘플 코드

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

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;

샘플 데이터