데이터베이스 질문과 답변
/* 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. 피벗 전에 데이터 부풀리기
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
'
감사합니다. 잘 됩니다.
-- 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 uName
SET @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)
) 버튼을 클릭하여 작성 하시면 됩니다.