MS SQL Server 行转列的总结

2017-10-27 13:28:09来源:oschina作者:MaxMax64人点击

分享

一直在找一个比较参数化的 行转列算法 (一个老话题了)今天看到一篇文章比较全面的介绍了的应用。


样本数据如图:

 方法一:使用拼接SQL,静态列字段;


  方法二:使用拼接SQL,动态列字段;


  方法三:使用PIVOT关系运算符,静态列字段;


  方法四:使用PIVOT关系运算符,动态列字段;

方法一:


SELECT [UserName],
  SUM(CASE [Subject] WHEN '数学' THEN [Source] ELSE 0 END) AS '[数学]',
  SUM(CASE [Subject] WHEN '英语' THEN [Source] ELSE 0 END) AS '[英语]',
  SUM(CASE [Subject] WHEN '语文' THEN [Source] ELSE 0 END) AS '[语文]'
  FROM [TestRows2Columns]
  GROUP BY [UserName]
  GO

 


结果:




方法二:


DECLARE @sql VARCHAR(8000)
  SET @sql = 'SELECT [UserName],'
  SELECT @sql = @sql + 'SUM(CASE [Subject] WHEN '''+[Subject]+''' THEN [Source] ELSE 0 END) AS '''+QUOTENAME([Subject])+''','
  FROM (SELECT DISTINCT [Subject] FROM [TestRows2Columns]) AS a
  SELECT @sql = LEFT(@sql,LEN(@sql)-1) + ' FROM [TestRows2Columns] GROUP BY [UserName]'
  PRINT(@sql)
  EXEC(@sql)
  GO

方法三:


SELECT *
  FROM ( SELECT [UserName] ,
  [Subject] ,
  [Source]
  FROM [TestRows2Columns]
  ) p PIVOT
  ( SUM([Source]) FOR [Subject] IN ( [数学],[英语],[语文] ) ) AS pvt
  ORDER BY pvt.[UserName];
  GO

方法四:各种必要的表名、分组列、行转列字段、字段值都已经参数化,直接修改就行作者:<听风吹雨>


DECLARE @sql_str NVARCHAR(MAX)
  DECLARE @sql_col NVARCHAR(MAX)
  DECLARE @tableName SYSNAME --行转列表
  DECLARE @groupColumn SYSNAME --分组字段
  DECLARE @row2column SYSNAME --行变列的字段
  DECLARE @row2columnValue SYSNAME --行变列值的字段
  SET @tableName = 'TestRows2Columns'
  SET @groupColumn = 'UserName'
  SET @row2column = 'Subject'
  SET @row2columnValue = 'Source'
  --从行数据中获取可能存在的列
  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)

各参数的说明入下图:


这个方法还有一个进阶版就是加入了查询条件:


  DECLARE @sql_str NVARCHAR(MAX)
  DECLARE @sql_col NVARCHAR(MAX)
  DECLARE @sql_where NVARCHAR(MAX)
  DECLARE @tableName SYSNAME --行转列表
  DECLARE @groupColumn SYSNAME --分组字段
  DECLARE @row2column SYSNAME --行变列的字段
  DECLARE @row2columnValue SYSNAME --行变列值的字段
  SET @tableName = 'TestRows2Columns'
  SET @groupColumn = 'UserName'
  SET @row2column = 'Subject'
  SET @row2columnValue = 'Source'
  SET @sql_where = 'WHERE UserName = ''王五''' --过滤条件
  --从行数据中获取可能存在的列
  SET @sql_str = N'
  SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])
  FROM ['+@tableName+'] '+@sql_where+' 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+']'+@sql_where+') p PIVOT
  (SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt
  ORDER BY pvt.['+@groupColumn+']'
  --PRINT (@sql_str)
  EXEC (@sql_str)

结果如图:

部分内容源自于听风吹雨

微信扫一扫

第七城市微信公众平台