SqlServer行转列

参考 ：https://www.cnblogs.com/johden2/p/5692765.html

例子演示

测试数据

/*-----1.行转列的测试数据--------------------------*/
IF OBJECT_ID('tbScore') IS NOT NULL 
    DROP TABLE tbScore

GO

CREATE TABLE tbScore
    (
      姓名 VARCHAR(10) ,
      课程 VARCHAR(10) ,
      分数 INT,
      日期 DATETIME
    )
GO

INSERT  INTO tbScore VALUES  ( '张三', '语文', 74,GETDATE() )
--INSERT  INTO tbScore VALUES  ( '张三', '数学', 83 ,GETDATE() )
INSERT  INTO tbScore VALUES  ( '张三', '物理', 93 ,GETDATE() )
INSERT  INTO tbScore VALUES  ( '李四', '语文', 74 ,GETDATE() )
INSERT  INTO tbScore VALUES  ( '李四', '数学', 84 ,GETDATE() )
INSERT  INTO tbScore VALUES  ( '李四', '物理', 94 ,GETDATE() )
GO

/*-----2.列转行的测试数据--------------------------*/
IF OBJECT_ID('tbScoreNew') IS NOT NULL 
    DROP TABLE tbScoreNew

GO

CREATE TABLE tbScoreNew(
      姓名 VARCHAR(10) ,
      语文 INT,
      数学 INT,
      物理 INT,
      日期 DATETIME
    )
GO

INSERT  INTO tbScoreNew VALUES  ( '李四', 74,84,94,GETDATE() )
INSERT  INTO tbScoreNew VALUES  ( '张三', 74,83,93,GETDATE() )
GO


/*-----3.动态增加列实现行转列(模拟组内项目要求)--------------------------*/
IF OBJECT_ID('tbDeptBudget') IS NOT NULL 
    DROP TABLE tbDeptBudget

GO
--部门预算
CREATE TABLE tbDeptBudget
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DeptCode VARCHAR(20),--部门编码
    DeptName NVARCHAR(100), --部门名称
    ProCode VARCHAR(20),--项目编码
    ProName NVARCHAR(100),--项目名称
    Year INT, --年度
    BudgetAmount DECIMAL(18,2), --预算金额
    FactAmount DECIMAL(18,2), --实际金额
    RemainAmount DECIMAL(18,2), --剩余金额
    CreateTime DATETIME  --创建时间
)
GO

INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','01','差旅费',2014,100000.00,80000.00,20000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','01','差旅费',2015,110000.00,90000.00,50000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','01','差旅费',2016,120000.00,100000.00,80000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','02','办公用品',2015,200000.00,150000.00,10000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('人事部','010000','02','办公用品',2016,160000.00,120000.00,80000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('财务部','020000','02','办公用品',2014,50000.00,40000.00,0.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('财务部','020000','02','办公用品',2015,50000.00,50000.00,10000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('财务部','020000','02','办公用品',2016,60000.00,50000.00,40000.00,GETDATE());
INSERT INTO tbDeptBudget(DeptName,DeptCode,ProCode,ProName,YEAR,BudgetAmount,FactAmount,RemainAmount,CreateTime)
VALUES('财务部','020000','03','采购费',2016,100000.00,80000.00,60000.00,GETDATE());

1、实现行转列

1. case WHEN实现

   /*-----1.1 Case WHEN 实现行转列----------*/
   
   --(1)静态SQL
   SELECT [姓名],
    max(CASE 课程 WHEN '语文' THEN 分数 ELSE 0 end) AS 语文,
    max(CASE 课程 WHEN '数学' THEN 分数 ELSE 0 end)AS 数学,
    max(CASE 课程 WHEN '物理' THEN 分数 ELSE 0 end)AS 物理,
    SUM(分数) AS 总分,
    AVG(分数) AS 平均分
   FROM tbScore GROUP BY [姓名]
   
   --(2)动态SQL
   DECLARE @sql VARCHAR(500)
   SET @sql = 'SELECT [姓名]'
   SELECT  @sql = @sql + ',MAX(CASE [课程] WHEN ''' + [课程] + ''' THEN [分数] ELSE 0 END)[' + [课程] + ']'
   FROM    ( 
               SELECT DISTINCT [课程] FROM tbScore
           ) T1
   --同FROM tbScore  GROUP BY [课程]，默认按课程名排序
   SET @sql = @sql + ' FROM tbScore GROUP BY [姓名]'
   PRINT '@sql: ' + @sql
   EXEC(@sql)
   

2. PIVOT 实现行转列，其中的NULL值发现还不好处理为0

   --(1)静态SQL
   SELECT  [姓名] ,
           [语文] ,
           [数学] ,
           [物理]
   FROM    ( SELECT    [分数] ,
                       [课程] ,
                       [姓名]
             FROM      tbScore
           ) AS SourceTable PIVOT ( AVG([分数]) FOR [课程] IN ( 语文, 数学, 物理 ) ) T
   
   
   --(2)动态SQL
   DECLARE @sql2 VARCHAR(8000)
   SET @sql2 = ''
   SELECT @sql2 = @sql2 + ',' + [课程] FROM dbo.tbScore GROUP BY [课程]
   --STUFF： 删除指定长度的字符，并在指定的起点处插入另一组字符。
   SET @sql2= STUFF(@sql2,1,1,'')  --去掉首个','
   SET @sql2 = 'SELECT [姓名],' + @sql2 + ' FROM (SELECT [分数],[课程],[姓名] FROM tbScore ) AS SourceTable PIVOT ( AVG([分数]) FOR [课程] IN ( ' + @sql2 + ') ) T'
   PRINT @sql2
   EXEC(@sql2)