데이터베이스 질문과 답변
/* PIVOT COLUMNS */
DECLARE @COLUMNS NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)
SET @COLUMNS = ''
SELECT @COLUMNS = @COLUMNS + '[' + uName + '],'
FROM (
SELECT uName
FROM LABORCOSTS
) AS months
ORDER BY uName
SET @COLUMNS = LEFT(@COLUMNS, LEN(@COLUMNS) - 1)
/* PIVOT COLUMNS */
SET @SQL = '
select *
from (
select orderno, ordernm,amt, gsname from #pivot
) as result
PIVOT (
SUM(amt) FOR gsname IN (' + @COLUMNS + ')
) as pivot_result
order by orderno
'
EXEC(@SQL)
여기서 NULL 일 경우 0으로 넣고 싶어요
아래처럼 해 봤는데 안되네요..
SUM(isnull(amt,0)) FOR gsname IN (' + @COLUMNS + ') X
SUM(amt) FOR gsname IN (' + ISNULL(@COLUMNS,0) + ') X
현재 결과는 첨부파일 처럼 표시 됩니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- 1. 피벗 전에 데이터 부풀리기SET @SQL = 'SELECT * FROM (SELECT a.orderno , a.ordernm , b.gsname , ISNULL(c.amt, 0) amt FROM (SELECT DISTINCT orderno, ordernm FROM #pivot) a CROSS JOIN laborcosts b LEFT OUTER JOIN #pivot c ON a.orderno = c.orderno AND b.gsname = c.gsname ) AS result PIVOT (SUM(amt) FOR gsname IN (' + @COLUMNS + ')) AS pivot_result ORDER BY orderno ' |
감사합니다. 잘 됩니다.
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 | -- 2. 피벗 대신 SUM(CASE) 사용하기/* SELECT_LIST COLUMNS */DECLARE @COLUMNS NVARCHAR(MAX)DECLARE @SQL NVARCHAR(MAX)SET @COLUMNS = ''SELECT @COLUMNS = @COLUMNS + ', ISNULL(SUM(CASE gsname WHEN ''' + uName + ''' THEN amt END), 0) AS ' + uName FROM ( SELECT uName FROM LABORCOSTS ) AS months ORDER BY uNameSET @COLUMNS = LEFT(@COLUMNS, LEN(@COLUMNS) - 1)/* PIVOT COLUMNS */SET @SQL = 'SELECT orderno , ordernm ' + @COLUMNS + ' FROM #pivot GROUP BY orderno, ordernm ORDER BY orderno 'EXEC(@SQL) |
) 버튼을 클릭하여 작성 하시면 됩니다.