sql server 行列转换

一、列转行

1、SqlServer 2005及以上版本（UNPIVOT ）
1.1、静态sql

SELECT 姓名,课程,分数 FROM tb UNPIVOT  (分数 FOR 课程 IN ([语文],[数学],[物理])) t

1.2、动态SQL

DECLARE @sql nvarchar(4000)
SELECT @sql=ISNULL(@sql+',','')+QUOTENAME(Name)
FROM syscolumns
WHERE ID=object_id('tb')AND Name NOT  IN ('姓名')
order BY Colid
SET @sql='select姓名,[课程],[分数] from tb UNPIVOT ([分数] for [课程] in('+@sql+')) b'
exec(@sql)

2、SqlServer 2000
2.1、静态sql（UNION ALL）

SELECT * FROM (
    SELECT [姓名],'语文' AS 课程,[语文] AS 分数 ,[日期] FROM tb
    UNION ALL
    SELECT [姓名],'数学' AS 课程,[数学] AS 分数 ,[日期] FROM tb
    UNION ALL
    SELECT [姓名],'物理' AS 课程,[物理] AS 分数 ,[日期] FROM tb
) T ORDER BY [姓名]

2.2、动态sql

SELECT @sql=ISNULL(@sql+' union all ','')+' select姓名,[课程]='+QUOTENAME(Name,'''')+',[分数] = '+QUOTENAME(Name)+' from tb'
FROM syscolumns
WHERE Name NOT IN('姓名') AND ID=object_id('tb')--表名tb，不包含列名为姓名的其他列
ORDER BY colid 
EXEC (@sql+' order by姓名')

二、行转列

1、SqlServer 2005及以上版本（PIVOT ）
1.1、静态SQL

SELECT  [姓名] ,
        [语文] ,
        [数学] ,
        [物理]
FROM    ( SELECT    [分数] ,
                    [课程] ,
                    [姓名]
          FROM      tbScore
        ) AS SourceTable PIVOT ( AVG([分数]) FOR [课程] IN ( 语文, 数学, 物理 ) ) T

1.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)

2、SqlServer 2000
2.1、静态SQL（Case WHEN）

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.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)