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([
      
        '
      
      
        +@row2column+
      
      
        '
      
      
        ]) 

FROM [
      
      
        '
      
      
        +@tableName+
      
      
        '
      
      ] GROUP BY [
      
        '
      
      
        +@row2column+
      
      
        '
      
      ]
      
        '


      
      --
      
        PRINT @sql_str

EXEC sp_executesql @sql_str,N
      
      
        '
      
      
        @sql_col_out NVARCHAR(MAX) OUTPUT
      
      
        '
      
      ,@sql_col_out=
      
        @sql_col OUTPUT


      
      --
      
        PRINT @sql_col



SET @sql_str 
      
      = N
      
        '


      
      SELECT *
      
         FROM (

SELECT [
      
      
        '
      
      
        +@groupColumn+
      
      
        '
      
      ],[
      
        '
      
      
        +@row2column+
      
      
        '
      
      ],[
      
        '
      
      
        +@row2columnValue+
      
      
        '
      
      ] FROM [
      
        '
      
      
        +@tableName+
      
      
        '
      
      
        ]) p PIVOT 

(SUM([
      
      
        '
      
      
        +@row2columnValue+
      
      
        '
      
      ]) FOR [
      
        '
      
      
        +@row2column+
      
      
        '
      
      ] IN ( 
      
        '
      
      
        + @sql_col +
      
      
        '
      
      
        ) ) AS pvt 

ORDER BY pvt.[
      
      
        '
      
      
        +@groupColumn+
      
      
        '
      
      ]
      
        '


      
      --
      
        PRINT (@sql_str)

EXEC (@sql_str)