sqlserver 行轉列

標籤：

第一種

SELECT title,
SUM(CASE typeid WHEN ‘1‘ THEN gjdate ELSE 0 END) AS ‘調休‘,
SUM(CASE typeid WHEN ‘2‘ THEN gjdate ELSE 0 END) AS ‘請假‘,
SUM(CASE typeid WHEN ‘3‘ THEN gjdate ELSE 0 END) AS ‘加班‘
FROM lfil_tb
GROUP BY title

第二種



DECLARE @sql_str NVARCHAR(MAX)DECLARE @sql_col NVARCHAR(MAX)DECLARE @tableName SYSNAME --表名DECLARE @groupColumn SYSNAME --分組欄位(title name 什麼玩意的)DECLARE @row2column SYSNAME --行轉列的欄位DECLARE @row2columnValue SYSNAME --行變列值的欄位SET @tableName = ‘lfil_tb‘SET @groupColumn = ‘title‘SET @row2column = ‘typeid‘SET @row2columnValue = ‘gjdate‘--從行資料中擷取可能存在的列SET @sql_str = N‘SELECT @sql_col_out = ISNULL(@sql_col_out + ‘‘,‘‘,‘‘‘‘) + QUOTENAME([‘[email protected]+‘]) FROM [‘[email protected]+‘] GROUP BY [‘[email protected]+‘]‘--PRINT @sql_strEXEC sp_executesql @sql_str,N‘@sql_col_out NVARCHAR(MAX) OUTPUT‘,@sql_col_out=@sql_col OUTPUT--PRINT @sql_colSET @sql_str = N‘SELECT * FROM (SELECT [‘[email protected]+‘],[‘[email protected]+‘],[‘[email protected]+‘] FROM [‘[email protected]+‘]) p PIVOT (SUM([‘[email protected]+‘]) FOR [‘[email protected]+‘] IN ( ‘+ @sql_col +‘) ) AS pvt ORDER BY pvt.[‘[email protected]+‘]‘--PRINT (@sql_str)EXEC (@sql_str)

 

sqlserver 行轉列