sqlserver行列转换

合集下载

SQLServer行列互转实现思路（聚合函数）

SQLServer⾏列互转实现思路（聚合函数）有时候会碰到⾏转列的需求（也就是将列的值作为列名称），通常我都是⽤ CASE END + 聚合函数来实现的。

如下：declare @t table(StudentName nvarchar(20),Subject nvarchar(20),Score int)Insert into @t (StudentName,Subject,Score) values ( '学⽣A', '中⽂', 80 );Insert into @t (StudentName,Subject,Score) values ( '学⽣A', '数学', 78 );Insert into @t (StudentName,Subject,Score) values ( '学⽣A', '英语', 92 );Insert into @t (StudentName,Subject,Score) values ( '学⽣B', '中⽂', 89 );Insert into @t (StudentName,Subject,Score) values ( '学⽣B', '数学', 87 );Insert into @t (StudentName,Subject,Score) values ( '学⽣B', '英语', 75 );Insert into @t (StudentName,Subject,Score) values ( '学⽣C', '中⽂', 92 );Insert into @t (StudentName,Subject,Score) values ( '学⽣C', '数学', 74 );Insert into @t (StudentName,Subject,Score) values ( '学⽣C', '英语', 65 );Insert into @t (StudentName,Subject,Score) values ( '学⽣D', '中⽂', 79 );Insert into @t (StudentName,Subject,Score) values ( '学⽣D', '数学', 83 );Insert into @t (StudentName,Subject,Score) values ( '学⽣D', '英语', 81 );Insert into @t (StudentName,Subject,Score) values ( '学⽣E', '中⽂', 73 );Insert into @t (StudentName,Subject,Score) values ( '学⽣E', '数学', 84 );Insert into @t (StudentName,Subject,Score) values ( '学⽣E', '英语', 93 );Insert into @t (StudentName,Subject,Score) values ( '学⽣F', '中⽂', 79 );Insert into @t (StudentName,Subject,Score) values ( '学⽣F', '数学', 86 );Insert into @t (StudentName,Subject,Score) values ( '学⽣F', '英语', 84 );select StudentName,sum(case when Subject = N'中⽂' then Score else 0 end) Chinese,sum(case when Subject = N'数学' then Score else 0 end) Math,sum(case when Subject = N'英语' then Score else 0 end) Engilshfrom @tgroup by StudentName今天看到⼀个新的写法，pivot 可以实现相同的功能（2005才开始⽀持）。

SQLServer-行列转换行转列，多行转多列-max函数用法

SQLServer-⾏列转换⾏转列，多⾏转多列-max函数⽤法效果如图，把同⼀个 code，按 cate 列分为 Actual 和 Budget 两⾏，再把mode 每种类型转换成列名，主要⽤到了 max 函数，很实⽤if exists(select*from tempdb..sysobjects where id=object_id('tempdb..#t'))drop table #tcreate table #t(code varchar(10), cname nvarchar(30),fyear varchar(30),cate varchar(10),mt numeric(18,4),amt numeric(18,2),mode nvarchar(20),mo_mt numeric(18,4),mo_avgfee numeric(18,2),mo_rate nvarchar(20) )insert into #tselect'400',N'深圳','2017','Actual','280','1400','BLK',10,1,'3.57%'union all select'400',N'深圳','2017','Actual','280','1400','V15',20,2,'7.14%'union all select'400',N'深圳','2017','Actual','280','1400','V5',30,3,'10.71%'union all select'400',N'深圳','2017','Actual','280','1400','V0',40,4,'14.29%'union all select'400',N'深圳','2017','Actual','280','1400','V20',50,5,'17.86%'union all select'400',N'深圳','2017','Actual','280','1400','V10',60,6,'21.43%'union all select'400',N'深圳','2017','Actual','280','1400','V25',70,7,'25.00%'union all select'400',N'深圳','2018','Budget','280','0','BLK',10,1,'3.57'union all select'400',N'深圳','2018','Budget','280','0','V15',20,2,'7.14%'union all select'400',N'深圳','2018','Budget','280','0','V5',30,3,'10.71%'union all select'400',N'深圳','2018','Budget','280','0','V0',40,4,'14.29%'union all select'400',N'深圳','2018','Budget','280','0','V20',50,5,'17.86%'union all select'400',N'深圳','2018','Budget','280','0','V10',60,6,'21.43%'union all select'400',N'深圳','2018','Budget','280','0','V25',70,7,'25.00%'select*from #t--增加⼀个强制mode 排序，⽐如从 vo v1 v2 依次排序if exists(select*from tempdb..sysobjects where id=object_id('tempdb..#sort'))drop table #sort create Table #sort ( mode varchar(10), )insert into #sortselect distinct mode FROM #t GROUP BY mode order by mode-- select * from #sortdeclare@sql nvarchar(max) --声明⼀个变量SET@sql='SELECT code '+',cname '+',fyear '+',cate '--+ N'''类别'''+',isnull(mt,0) mt'-- + N'''吨数'''+',isnull(amt,0) amt'-- + N'''⾦额'''select@sql=@sql+' , max(case mode when '''+ mode+''' then mo_rate else '''' end) ['+ mode+']'+' , max(case mode when '''+ mode+''' then mo_avgfee else 0 end) ['+ mode+'_unit]'from (select mode FROM #sort ) as a--print @sqlset@sql=@sql+' from #t group by code, cname,fyear,cate,mt,amt order by code,fyear 'print@sqlexec(@sql) --执⾏该sql。

sqlserver行列转换

sqlserver⾏列转换sqlserver⾏转列--创建⾏转列表及插⼊数据create table tb_RowConvertToColumn(username nvarchar(100) null,course nvarchar(100) null,score numeric(10,2) null)insert into tb_RowConvertToColumn(username,course,score) values('张三','语⽂',82)insert into tb_RowConvertToColumn(username,course,score) values('张三','数学',85)insert into tb_RowConvertToColumn(username,course,score) values('张三','外语',90)insert into tb_RowConvertToColumn(username,course,score) values('李四','语⽂',86)insert into tb_RowConvertToColumn(username,course,score) values('李四','数学',82)insert into tb_RowConvertToColumn(username,course,score) values('李四','外语',92)insert into tb_RowConvertToColumn(username,course,score) values('王五','语⽂',82)insert into tb_RowConvertToColumn(username,course,score) values('王五','数学',94)insert into tb_RowConvertToColumn(username,course,score) values('王五','外语',82)--1.静态sql⾏转列，该sql指定了转换的列头select username 姓名,MAX(case course when'语⽂'then score else0end) 语⽂,MAX(case course when'数学'then score else0end) 数学,MAX(case course when'外语'then score else0end) 外语from tb_RowConvertToColumngroup by usernameorder by username/*姓名语⽂数学外语李四 86.00 82.00 92.00王五 82.00 94.00 82.00张三 82.00 85.00 90.00*/--2.静态sql⾏转列，该sql指定了转换的列头，该语句必须sqlserver2005及以上版本才能使⽤select username 姓名,语⽂,数学,外语from tb_RowConvertToColumn pivot(max(score) for course in(语⽂,数学,外语)) a /*姓名语⽂数学外语李四 86.00 82.00 92.00王五 82.00 94.00 82.00张三 82.00 85.00 90.00*/select*from tb_RowConvertToColumn pivot(max(score)for course in (语⽂,数学,外语)) a/*username 语⽂数学外语李四 86.00 82.00 92.00王五 82.00 94.00 82.00张三 82.00 85.00 90.00*/--3.动态sql⾏转列，⾃动⽣成转换的列declare@sql nvarchar(2000)select distinct course into #tb_group from tb_RowConvertToColumn order by course desc--表头及排序select@sql=ISNULL(@sql+',','')+'MAX(case course when '''+course+''' then score else 0 end) ['+course+']'from #tb_groupset@sql='select username 姓名,'+@sql+' from tb_RowConvertToColumn a'+' group by username'exec(@sql)drop table #tb_group/*姓名语⽂外语数学李四 86.00 92.00 82.00王五 82.00 82.00 94.00张三 82.00 90.00 85.00*/--4.动态sql⾏转列，⾃动⽣成转换的列，该语句必须sqlserver2005及以上版本才能使⽤declare@sql nvarchar(2000)select@sql=ISNULL(@sql+',','')+coursefrom tb_RowConvertToColumngroup by courseset@sql='select * from tb_RowConvertToColumn pivot (max(score) for course in ('+@sql+')) a'exec(@sql)/*username 数学外语语⽂李四 82.00 92.00 86.00王五 94.00 82.00 82.00张三 85.00 90.00 82.00*/sqlserver列转⾏--创建列转⾏表及插⼊数据create table tb_ColumnConvertToRow([姓名]nvarchar(100) null,[语⽂]nvarchar(100) null,[数学]nvarchar(100) null,[外语]nvarchar(100) null)insert into tb_ColumnConvertToRow(姓名,语⽂,数学,外语) values('李四',82,92,86)insert into tb_ColumnConvertToRow(姓名,语⽂,数学,外语) values('王五',94,82,82)insert into tb_ColumnConvertToRow(姓名,语⽂,数学,外语) values('张三',85,90,82)--1.静态sql列转⾏,当列头较少时使⽤select*from(select姓名,课程='语⽂',分数=语⽂from tb_ColumnConvertToRowunion allselect姓名,课程='数学',分数=数学from tb_ColumnConvertToRowunion allselect姓名,课程='外语',分数=外语from tb_ColumnConvertToRow) a/*姓名课程分数李四语⽂ 82王五语⽂ 94张三语⽂ 85李四数学 92王五数学 82张三数学 90李四外语 86王五外语 82张三外语 82*/--2.静态sql列转⾏,当列头较少时使⽤，该语句必须sqlserver2005及以上版本才能使⽤select姓名,课程,分数from tb_ColumnConvertToRow unpivot(分数for课程in (语⽂,数学,外语)) a /*姓名课程分数李四语⽂ 82李四数学 92李四外语 86王五语⽂ 94王五数学 82王五外语 82张三语⽂ 85张三数学 90张三外语 82*/。

sqlserver行列转换

from TB_Product_Field b group by b.FKProductClassID,b.FKProductID
方法二：
select FKProductID,FKProductClassID,max(Material) as Material,max(Specification) as Specification from TB_Product_Field pivot (
max(FieldValue) for FieldName in([Material],[Specification]) )as pvt group by FKProductClassID,FKProductID
我用的方法二
嘿嘿，说下我是在什么情况下使用的。最近做了个项目，需求是像阿里巴巴一样，根据选择的产品类别，动态调用相应的字段，所以就想到了这种方法。FieldName和FildValue的巴一样根据选择的产品类别动态调用相应的字段所以就想到了这种方法
sqlserver行列转换
我是对FieldName和FildValue做的转换，目标是Material和Specification
原先的数据：
行列转换之后的数据：
具体的SQL代码如下：
方法一：
select b.FKProductID,b.FKProductClassID ,max(case when b.FieldName='Material' then b.FieldValue end) Material ,max(case when b.FieldName='Specification' then b.FieldValue end) Specification

SQL Server 动态行转列(参数化表名、分组列、行转列字段、字段值)

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQL Codes）方法一：使用拼接SQL，静态列字段；方法二：使用拼接SQL，动态列字段；方法三：使用PIVOT关系运算符，静态列字段；方法四：使用PIVOT关系运算符，动态列字段；扩展阅读一：参数化表名、分组列、行转列字段、字段值；扩展阅读二：在前面的基础上加入条件过滤；二.背景（Contexts）其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了。

行转列的效果图如图1所示：（图1：行转列效果图）(一) 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示：--创建测试表IF EXISTS (SELECT*FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[TestRows2Columns]') AND type in (N'U'))DROP TABLE[dbo].[TestRows2Columns]GOCREATE TABLE[dbo].[TestRows2Columns]([Id][int]IDENTITY(1,1) NOT NULL,[UserName][nvarchar](50) NULL,[Subject][nvarchar](50) NULL,[Source][numeric](18, 0) NULL) ON[PRIMARY]GO--插入测试数据INSERT INTO[TestRows2Columns] ([UserName],[Subject],[Source])SELECT N'张三',N'语文',60UNION ALLSELECT N'李四',N'数学',70UNION ALLSELECT N'王五',N'英语',80UNION ALLSELECT N'王五',N'数学',75UNION ALLSELECT N'王五',N'语文',57UNION ALLSELECT N'李四',N'语文',80UNION ALLSELECT N'张三',N'英语',100GOSELECT*FROM[TestRows2Columns]（图2：样本数据）(二) 先以静态的方式实现行转列，效果如图3所示：--1：静态拼接行转列SELECT[UserName],SUM(CASE[Subject]WHEN'数学'THEN[Source]ELSE0END) AS'[数学]',SUM(CASE[Subject]WHEN'英语'THEN[Source]ELSE0END) AS'[英语]',SUM(CASE[Subject]WHEN'语文'THEN[Source]ELSE0END) AS'[语文]'FROM[TestRows2Columns]GROUP BY[UserName]GO（图3：样本数据）(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；--2：动态拼接行转列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 aSELECT@sql=LEFT(@sql,LEN(@sql)-1) +' FROM [TestRows2Columns] GROUP BY [UserName]'PRINT(@sql)EXEC(@sql)GO(四) 在SQL Server 2005之后有了一个专门的PIVOT 和UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：--3：静态PIVOT行转列SELECT*FROM ( SELECT[UserName] ,[Subject] ,[Source]FROM[TestRows2Columns]) p PIVOT( SUM([Source]) FOR[Subject]IN ( [数学],[英语],[语文] ) ) AS pvtORDER BY pvt.[UserName];GO（图4）(五) 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：--4：动态PIVOT行转列DECLARE@sql_str VARCHAR(8000)DECLARE@sql_col VARCHAR(8000)SELECT@sql_col=ISNULL(@sql_col+',','') +QUOTENAME([Subject]) FROM[TestRows2Columns]GROUP BY[Subject]SET@sql_str='SELECT * FROM (SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT(SUM([Source]) FOR [Subject] IN ( '+@sql_col+') ) AS pvtORDER BY pvt.[UserName]'PRINT (@sql_str)EXEC (@sql_str)(六) 也许很多人到了上面一步就够了，但是你会发现，当别人拿到你的代码，需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：--5：参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================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_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@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)（图5）(七) 在实际的运用中，我经常遇到需要对基础表的数据进行筛选后再进行行转列，那么下面的脚本将满足你这个需求，效果如图6所示：--6：带条件查询的参数化动态PIVOT行转列-- =============================================-- Author: <听风吹雨>-- Create date: <2014.05.26>-- Description: <参数化动态PIVOT行转列，带条件查询的参数化动态PIVOT行转列>-- Blog: <:///gaizai/>-- =============================================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_strEXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT --PRINT @sql_colSET@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)（图6）。

SQLServer基础之行数据转换为列数据

SQLServer基础之⾏数据转换为列数据准备⼯作创建表use [test1]gocreate table [dbo].[student]([id] [int] identity(1,1) not null,[name] [nvarchar](50) null,[project] [nvarchar](50) null,[score] [int] null,constraint [pk_student] 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插⼊数据insert into test1.dbo.student(name,project,score)values('张三','android','60'),('张三','ios','70'),('张三','html5','55'),('张三','.net','100'),('李四','android','60'),('李四','ios','75'),('李四','html5','90'),('李四','.net','100');使⽤Case When和聚合函数进⾏⾏专列语法select column_name,<aggregation function>(<case when expression>)from database.schema.tablegroup by column_name语法解析column_name数据列列名aggregation function聚合函数，常见的有：sum,max,min,avg,count等。

sqlserver行转列函数

sqlserver行转列函数SQL Server中有多种方法可以将行转列，包括使用PIVOT函数、CASE语句和动态SQL等。

在接下来的讨论中，我将介绍这些方法，并提供示例来帮助您理解。

1.使用PIVOT函数：PIVOT函数是SQL Server中实现行转列功能的内置函数。

它将一个由行组成的结果集转换为具有动态列的结果集。

以下是使用PIVOT函数将行转列的示例：```sqlSELECT*FROMSELECT employee_id, department, salaryFROM employeesAS srPIVOTSUM(salary)FOR department IN ([IT], [Finance], [HR])AS piv```在上面的示例中，我们选择员工ID、部门和薪水，并使用PIVOT函数将部门作为列转换。

每个部门的薪水总和将作为新的列显示。

2.使用CASE语句：CASE语句是一种常见的在SQL中实现行转列的方法。

通过使用CASE 语句，我们可以将满足特定条件的行值转换为动态列。

以下是使用CASE语句进行行转列的示例：```sqlSELECT employee_id,MAX(CASE WHEN department = 'IT' THEN salary END) AS IT,MAX(CASE WHEN department = 'Finance' THEN salary END) AS Finance,MAX(CASE WHEN department = 'HR' THEN salary END) AS HRFROM employeesGROUP BY employee_id;```在上面的示例中，我们首先通过GROUPBY将结果按照员工进行分组，然后使用CASE语句将不同部门的薪水作为新的列。

3.使用动态SQL：动态SQL是一种使用SQL Server中的字符串操作来构建和执行动态查询的方法。

SQLServer行列相互转换命令：PIVOT和UNPIVOT使用详解

SQLServer⾏列相互转换命令：PIVOT和UNPIVOT使⽤详解⼀、版本要求1.数据库的最低版本要求为SQL Server 2005 或更⾼。

2.必须将数据库的兼容级别设置为90 或更⾼。

3.查看我的数据库版本及兼容级别。

如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management Studio新建⼀个查询窗⼝输⼊：print @@version，运⾏之后在我的本机上得到：Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)Apr 2 2010 15:53:02Copyright (c) Microsoft CorporationExpress Edition with Advanced Services on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)然后我们选择⼀个数据库然后右键-属性选择[选项]得到下图的信息。

在确认数据库的版本和兼容级别符合1,2点的要求后你才可以接着继续往下学习。

⼆、PIVOT 实现⾏转列1.在这⾥我们先构建⼀个测试数据表(这⾥使⽤的是临时表，以⽅便我们在退出会话的时候⾃动删除表及其数据)⾸先我们先设计⼀个表架构为#Student { 学⽣编号[PK], 姓名, 性别, 所属班级 }的表,然后编写如下T-SQL--创建临时表(仅演⽰,表结构的不合理还请包涵)CREATE TABLE #Student ([学⽣编号]INT IDENTITY(1, 1) PRIMARY KEY, [姓名]NVARCHAR(20), [性别]NVARCHAR(1), [所属班级]NVARCHAR(20))--给临时表插⼊数据INSERT INTO #Student ([姓名], [性别], [所属班级])SELECT'李妹妹', '⼥', '初⼀ 1班'UNION ALLSELECT'泰强', '男', '初⼀ 1班'UNION ALLSELECT'泰映', '男', '初⼀ 1班'UNION ALLSELECT'何谢', '男', '初⼀ 1班'UNION ALLSELECT'李春', '男', '初⼆ 1班'UNION ALLSELECT'吴歌', '男', '初⼆ 1班'UNION ALLSELECT'林纯', '男', '初⼆ 1班'UNION ALLSELECT'徐叶', '⼥', '初⼆ 1班'UNION ALLSELECT'龙门', '男', '初三 1班'UNION ALLSELECT'⼩红', '⼥', '初三 1班'UNION ALLSELECT'⼩李', '男', '初三 1班'UNION ALLSELECT'⼩黄', '⼥', '初三 2班'UNION ALLSELECT'旺财', '男', '初三 2班'UNION ALLSELECT'强强', '男', '初⼆ 1班';以下是查询的结果：2.查询各班级的总⼈数SELECT[所属班级]AS[班级], COUNT(1) AS[⼈数]FROM #Student GROUP BY[所属班级]ORDER BY[⼈数]DESC班级⼈数-------- -----------初⼆ 1班5初⼀ 1班4初三 1班3初三 2班2好了，在这⾥我希望把上⾯的表{ 班级, ⼈数 } 由班级[⾏] 的显⽰转换为班级[列] 的显⽰格式!在此你会看到第⼀个PIVOT⽰例。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

SQL Server中行列转换Pivot UnPivotPIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE 语句实现PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P完整语法：table_sourcePIVOT(聚合函数（value_column）FOR pivot_columnIN(<column_list>))UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现完整语法：table_sourceUNPIVOT(value_columnFOR pivot_columnIN(<column_list>))注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别在数据库属性->选项->兼容级别改为 90典型实例一、行转列1、建立表格if object_id('tb')isnotnull droptable tbgocreatetable tb(姓名varchar(10),课程varchar(10),分数int) insertinto tb values('张三','语文',74)insertinto tb values('张三','数学',83)insertinto tb values('张三','物理',93)insertinto tb values('李四','语文',74)insertinto tb values('李四','数学',84)insertinto tb values('李四','物理',94)goselect*from tbgo姓名课程分数---------- ---------- -----------张三语文 74张三数学 83张三物理 93李四语文 74李四数学 84李四物理 942、使用SQL Server 2000静态SQL--cselect姓名,max(case课程when'语文'then分数else0end)语文,max(case课程when'数学'then分数else0end)数学,max(case课程when'物理'then分数else0end)物理from tbgroupby姓名姓名语文数学物理---------- ----------- ----------- -----------李四 74 84 94张三 74 83 933、使用SQL Server 2000动态SQL--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。

(以下同) --变量按sql语言顺序赋值declare@sql varchar(500)set@sql='select姓名'select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'from(selectdistinct课程from tb)a--同from tb group by课程，默认按课程名排序set@sql=@sql+' from tb group by姓名'exec(@sql)--使用isnull(),变量先确定动态部分declare@sql varchar(8000)select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end) ['+课程+']'from(selectdistinct课程from tb)as aset@sql='select姓名,'+@sql+' from tb group by姓名'exec(@sql)姓名数学物理语文---------- ----------- ----------- -----------李四 84 94 74张三 83 93 744、使用SQL Server 2005静态SQLselect*from tb pivot(max(分数)for课程in(语文,数学,物理))a5、使用SQL Server 2005动态SQL--使用stuff()declare@sql varchar(8000)set@sql=''--初始化变量@sqlselect@sql=@sql+','+课程from tb groupby课程--变量多值赋值set@sql=stuff(@sql,1,1,'')--去掉首个','set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a' exec(@sql)--或使用isnull()declare@sql varchar(8000)–-获得课程集合select@sql=isnull(@sql+',','')+课程from tb groupby课程set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a' exec(@sql)二、行转列结果加上总分、平均分1、使用SQL Server 2000静态SQL--SQL SERVER 2000静态SQLselect姓名,max(case课程when'语文'then分数else0end)语文,max(case课程when'数学'then分数else0end)数学,max(case课程when'物理'then分数else0end)物理,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分from tbgroupby姓名姓名语文数学物理总分平均分---------- ----------- ----------- ----------- -----------李四 74 84 94 252 84.00张三 74 83 93 250 83.332、使用SQL Server 2000动态SQL--SQL SERVER 2000动态SQLdeclare@sql varchar(500)set@sql='select姓名'select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'from(selectdistinct课程from tb)aset@sql=@sql+',sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分from tb group by姓名'exec(@sql)3、使用SQL Server 2005静态SQLselect m.*,n.总分,n.平均分from(select*from tb pivot(max(分数)for课程in(语文,数学,物理))a)m,(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分from tbgroupby姓名)nwhere m.姓名=n.姓名4、使用SQL Server 2005动态SQL--使用stuff()--declare@sql varchar(8000)set@sql=''--初始化变量@sqlselect@sql=@sql+','+课程from tb groupby课程--变量多值赋值--同select @sql = @sql + ','+课程from (select distinct课程from tb)aset@sql=stuff(@sql,1,1,'')--去掉首个','set@sql='select m.* , n.总分,n.平均分from(select * from (select * from tb) a pivot (max(分数) for课程in ('+@sql+')) b) m ,(select姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2))平均分from tb group by姓名) nwhere m.姓名= n.姓名'exec(@sql)--或使用isnull()declare@sql varchar(8000)select@sql=isnull(@sql+',','')+课程from tb groupby课程set@sql='select m.* , n.总分,n.平均分from(select * from (select * from tb) a pivot (max(分数) for课程in ('+@sql+')) b) m ,(select姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2))平均分from tb group by姓名) nwhere m.姓名= n.姓名'exec(@sql)二、列转行1、建立表格if object_id('tb')isnotnull droptable tbgocreatetable tb(姓名varchar(10),语文int,数学int,物理int)insertinto tb values('张三',74,83,93)insertinto tb values('李四',74,84,94)goselect*from tbgo姓名语文数学物理---------- ----------- ----------- -----------张三 74 83 93李四 74 84 942、使用SQL Server 2000静态SQL--SQL SERVER 2000静态SQL。