--*****************SQL 行转列*************************** --***************行转列*************************** --实现课程列值旋转为列名 --SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。 --使用SQL SERVER 2005静态SQL --使用SQL Server 2005动态SQL --2使用ISNULL() /*运行结果： 姓名 语文 数学 物理 ---------- ----------- ----------- ----------- 张三 74 83 93 李四 74 84 94 */ --行转列结果加上总分、平均分 /*运行结果： 姓名 语文 数学 物理 总分 平均分 ----------------- ----------- ---- 李四 74 84 94 252 84.00 张三 74 83 93 250 83.33 */
-- 创建实例表 IF OBJECT_ID( ' TB ') IS NOT NULL DROP TABLE TB GO -- 构建实例数据 CREATE TABLE TB(姓名 NVARCHAR( 10),课程 NVARCHAR( 10),分数 INT) INSERT INTO TB VALUES(N ' 张三 ',N ' 语文 ', 74) INSERT INTO TB VALUES(N ' 张三 ',N ' 数学 ', 83) INSERT INTO TB VALUES(N ' 张三 ',N ' 物理 ', 93) INSERT INTO TB VALUES(N ' 李四 ',N ' 语文 ', 74) INSERT INTO TB VALUES(N ' 李四 ',N ' 数学 ', 84) INSERT INTO TB VALUES(N ' 李四 ',N ' 物理 ', 94) SELECT * FROM TB WITH(NOLOCK) GO
-- 使用SQL SERVER 2000静态SQL SELECT 姓名 , MAX( CASE 课程 WHEN N ' 语文 ' THEN 分数 ELSE 0 END) AS N ' 语文 ' , MAX( CASE 课程 WHEN N ' 数学 ' THEN 分数 ELSE 0 END) AS N ' 数学 ' , MAX( CASE 课程 WHEN N ' 物理 ' THEN 分数 ELSE 0 END) AS N ' 物理 ' FROM TB GROUP BY 姓名
-- 变量按SQL语言顺序赋值 DECLARE @SQL1 NVARCHAR( MAX) SET @SQL1 =N ' SELECT 姓名 ' SELECT @SQL1 = @SQL1 +N ' ,MAX(CASE 课程 WHEN N ''' +课程 +N ''' THEN 分数 ELSE 0 END)[ ' +课程 + ' ] ' FROM( SELECT DISTINCT 课程 FROM TB )A SET @SQL1 = @SQL1 +N ' FROM TB GROUP BY 姓名 ' EXEC( @SQL1)
-- 使用ISNULL(),变量先确定动态部分 DECLARE @SQL NVARCHAR( MAX) SELECT @SQL = ISNULL( @SQL + ' , ', '') +N ' MAX(CASE 课程 WHEN N ''' +课程 + N ''' THEN 分数 ELSE 0 END) [ ' +课程 +N ' ] ' FROM( SELECT DISTINCT 课程 FROM TB ) AS A SET @SQL =N ' SELECT 姓名, ' + @SQL +N ' FROM TB GROUP BY 姓名 ' EXEC( @SQL)
PIVOT--列值转换为列名，即行转列
PIVOT(聚合函数(列) FOR 列 in (…) )AS P
完整语法：
Table_Source PIVOT ( 聚合函数【 MAX, SUM,等】（value_column） FOR pivot_column【要成为列标题的值的列】 IN(列值 of pivot_column) ) 别命
SELECT 姓名 ,语文 ,数学 ,物理 FROM TB PIVOT ( MAX(分数) FOR 课程 IN ( 语文 ,数学 ,物理 ) )A
-- 1使用STUFF( character_expression , start , length ,character_expression ),start 从索引1开始 DECLARE @SQL2 NVARCHAR( MAX) SET @SQL2 = '' SELECT @SQL2 = @SQL2 + ' , ' +课程 FROM TB GROUP BY 课程 PRINT @SQL2 SET @SQL2 = STUFF( @SQL2, 1, 1, '') -- 去掉首个',' PRINT @SQL2 SET @SQL2 =N ' SELECT * FROM TB PIVOT (MAX(分数) FOR 课程 IN ( ' + @SQL2 + ' ))A ' EXEC( @SQL2)
DECLARE @SQL3 NVARCHAR( MAX) SELECT @SQL3 = ISNULL( @SQL3 + ' , ', '') +课程 FROM TB GROUP BY 课程 SET @SQL3 =N ' SELECT * FROM TB PIVOT (MAX(分数) FOR 课程 IN ( ' + @SQL3 + ' ))A ' EXEC( @SQL3)
-- SQL SERVER 2000静态SQL BEGIN SELECT 姓名 , MAX( CASE 课程 WHEN N ' 语文 ' THEN 分数 ELSE 0 END) 数学 , MAX( CASE 课程 WHEN N ' 数学 ' THEN 分数 ELSE 0 END) 物理 , MAX( CASE 课程 WHEN N ' 物理 ' THEN 分数 ELSE 0 END) 语文 , SUM(分数) AS 总分 , CAST( AVG(分数 * 1.0) AS DECIMAL( 4, 2)) AS 平均分 FROM TB GROUP BY 姓名 END
-- SQL SERVER 2000动态SQL BEGIN DECLARE @SQL4 nvarchar( MAX) SELECT @SQL4 = ISNULL( @SQL4 + ' , ', '') +N ' MAX(CASE 课程 WHEN N ''' +课程 +N ''' THEN 分数 ELSE 0 END) ' +课程 FROM ( SELECT DISTINCT 课程 FROM TB ) AS TT SET @SQL4 =N ' SELECT 姓名 , ' + @SQL4 ++N ' ,SUM(分数)AS 总分 ,CAST(AVG(分数*1.0)AS decimal(4,2)) AS 平均分 FROM TB GROUP BY 姓名 ' PRINT @SQL4 EXEC( @sql4) END
-- SQL SERVER 2005 静态PIVOT BEGIN SELECT A. * ,B.总分 ,B.平均分 FROM ( SELECT * FROM TB PIVOT ( MAX(分数) FOR 课程 IN ( 数学 ,语文 ,物理 ) ) AS TT ) AS A INNER JOIN ( SELECT 姓名 , SUM(分数) AS 总分 , CAST( AVG(分数 * 1.0) AS decimal( 4, 2)) AS 平均分 FROM TB GROUP BY 姓名 ) AS B ON A.姓名 =B.姓名 END
-- SQL SERVER 2005 动态PIVOT BEGIN DECLARE @SQL5 NVARCHAR( MAX) SELECT @SQL5 = ISNULL( @SQL5 + ' , ', '') + 课程 FROM TB GROUP BY 课程 SET @SQL5 =N ' SELECT * FROM TB PIVOT (MAX(分数) FOR 课程 IN ( ' + @SQL5 + ' )) AS TT ' SET @SQL5 =N ' SELECT A.*,B.总分,B.平均分 FROM ( ' + @SQL5 +N ' ) AS A INNER JOIN (SELECT 姓名,SUM(分数) AS 总分,CAST(AVG(分数) AS DECIMAL(4,2)) AS 平均分 FROM TB GROUP BY 姓名) AS B ON A.姓名=B.姓名 ' PRINT @SQL5 EXEC SP_EXECUTESQL @SQL5 END
--另一个例子 --行转换列:四个季度的利润转换成横向显示
-- Create 样例表 IF OBJECT_ID( ' TEST ') IS NOT NULL BEGIN DROP TABLE TEST END CREATE TABLE TEST ( ID INT ,Name VARCHAR( 20) , [ Quarter ] INT , [ Profile ] INT )
-- 构建样例数据 INSERT INTO TEST VALUES( 1, ' A ', 1, 1000) INSERT INTO TEST VALUES( 1, ' A ', 2, 2000) INSERT INTO TEST VALUES( 1, ' A ', 3, 4000) INSERT INTO TEST VALUES( 1, ' A ', 4, 5000) INSERT INTO TEST VALUES( 1, ' B ', 1, 3000) INSERT INTO TEST VALUES( 1, ' B ', 2, 3500) INSERT INTO TEST VALUES( 1, ' B ', 3, 4200) INSERT INTO TEST VALUES( 1, ' B ', 4, 5500)
-- SQL SERVER 2000 静态SQL SELECT ID ,Name , SUM( CASE [ Quarter ] WHEN 1 THEN [ Profile ] ELSE 0 END) AS N ' 1季度 ' , SUM( CASE [ Quarter ] WHEN 2 THEN [ Profile ] ELSE 0 END) AS N ' 2季度 ' , SUM( CASE [ Quarter ] WHEN 3 THEN [ Profile ] ELSE 0 END) AS N ' 3季度 ' , SUM( CASE [ Quarter ] WHEN 4 THEN [ Profile ] ELSE 0 END) AS N ' 4季度 ' FROM TEST GROUP BY ID,Name
-- SQL SERVER 2005 静态PIVOT SELECT * FROM TEST PIVOT ( SUM( [ Profile ]) FOR [ Quarter ] IN ( [ 1 ], [ 2 ], [ 3 ], [ 4 ]) ) AS TT