MSSQL横列转纵列

TABLE [dbo].[AcrossChangeEndLong]( [Id] [INT] IDENTITY(1,1) NOT NULL, [Name] [NVARCHAR](50) NOT NULL, [Subject] [NVARCHAR](50) NOT NULL, [Score] [INT] NOT NULL, CONSTRAINT [PK_AcrossChangeEndLong] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[AcrossChangeEndLong] ADD CONSTRAINT [DF_AcrossChangeEndLong_Score] DEFAULT ((0)) FOR [Score] GO

2.具体SQL

SELECT  Name AS ‘姓名‘,
        MAX(CASE [Subject]
              WHEN ‘语文‘ THEN Score
              ELSE 0
            END) AS ‘语文‘ ,
        MAX(CASE [Subject]
              WHEN ‘英语‘ THEN Score
              ELSE 0
            END) AS ‘英语‘ ,
        MAX(CASE [Subject]
              WHEN ‘数学‘ THEN Score
              ELSE 0
            END) AS ‘数学‘
FROM    dbo.AcrossChangeEndLong
GROUP BY Name ORDER BY Name

如果到时候增加了科目，比如增加了化学,这时候为了再次修改，我们可以弄成动态的(根据列自动增加),这里使用的是动态拼接SQL,会根据科目的增加而增加列,具体SQL如下

DECLARE @sql VARCHAR(8000)
SET @sql = ‘SELECT [Name],‘   
SELECT  @sql = @sql + ‘SUM(CASE [Subject] WHEN ‘‘‘ + [Subject]
        + ‘‘‘ THEN [Score] ELSE 0 END) AS ‘‘‘ + [Subject] + ‘‘‘,‘
FROM    ( SELECT DISTINCT
                    [Subject]
          FROM      dbo.AcrossChangeEndLong
        ) AS a     
SELECT  @sql = LEFT(@sql, LEN(@sql) - 1)
        + ‘ FROM [AcrossChangeEndLong] GROUP BY [Name]‘   
PRINT ( @sql )
EXEC(@sql)

 

 另外在SQL Server 2005之后有了一个专门的PIVOT 和 UNPIVOT 关系运算符做行列之间的转换，具体SQL如下

SELECT  *
FROM    ( SELECT    Name AS ‘姓名‘,
                    Subject ,
                    Score
          FROM      dbo.AcrossChangeEndLong
        ) p PIVOT ( MAX(Score) FOR Subject IN ( [数学], [英语], [语文] ) ) AS pvt
ORDER BY pvt.姓名

 使用Pivot动态拼接SQL：

DECLARE @sql_str VARCHAR(MAX)
DECLARE @sql_col VARCHAR(MAX)
SELECT  @sql_col = ISNULL(@sql_col + ‘,‘, ‘‘) + QUOTENAME([Subject])
FROM    dbo.AcrossChangeEndLong
GROUP BY [Subject]
SET @sql_str = ‘
SELECT * FROM (
    SELECT [Name],[Subject],[Score] FROM [AcrossChangeEndLong]) p PIVOT 
    (SUM([Score]) FOR [Subject] IN ( ‘ + @sql_col + ‘) ) AS pvt 
ORDER BY pvt.[Name]‘
PRINT ( @sql_str )
EXEC (@sql_str)

 

具体效果：  

 

 

扩展：可能我们需要加一个统计行，我们可以用UNION ALL连接 上一个表的结果【可以将结果存入临时表】,再用函数SUM

 

MSSQL横列转纵列

标签：