동적 pivot

MS-SQL

동적 pivot

캡틴노랑이 2016. 10. 5. 17:39

동적 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;

샘플 데이터

저작자표시 비영리 변경금지 (새창열림)