SqlServer普通行列转换
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基础之行数据转换为列数据
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行转列和列转行
1：行转列子查询，获取一定数据集结果SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN (SELECT TOP 10 objid FROM T_MyAttentiontma GROUP BY objid ORDER BY count(1) DESC)GROUP BY objid,action下面用行转列语法获取最终结果select *from(SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN (SELECT TOP 10 objid FROM T_MyAttentiontma GROUP BY objid ORDER BY count(1) DESC)GROUP BY objid,action) tpivot ( sum(count) for t.action in ([1],[2],[3],[4])) as ourpivot微软官方的图：2：列转行怎么把一条记录拆分成几条记录？User No. A B C1 1 21 34 241 2 42 25 16 RESULT:User No. Type Num1 1 A 211 1 B 341 1 C 241 2 A 421 2 B 251 2 C 16declare @t table(usserint ,no int ,a int,bint, c int) insert into @t select 1,1,21,34,24union all select 1,2,42,25,16SELECT usser,no,Type=attribute, Num=valueFROM @tUNPIVOT(value FOR attribute IN([a], [b], [c])) AS UPV列转行备注value FOR attribute IN([a], [b], [c])这句话中，a,b,c是列的名字，但是列名不能出现在上句的select语句中。
sqlserver行列转换
sqlserver⾏列转换我们在写Sql语句的时候没经常会遇到将查询结果⾏转列，列转⾏的需求，拼接sql字符串，然后使⽤sp_executesql执⾏sql字符串是⽐较常规的⼀种做法。
但是这样做实现起来⾮常复杂，⽽在SqlServer2005中我们有了PIVOT/UNPIVOT函数可以快速实现⾏转列和列转⾏的操作。
PIVOT函数，⾏转列PIVOT函数的格式如下PIVOT(<聚合函数>([聚合列值]) FOR [⾏转列前的列名] IN([⾏转列后的列名1],[⾏转列后的列名2],[⾏转列后的列名3],.......[⾏转列后的列名N])) <聚合函数>就是我们使⽤的SUM,COUNT,AVG等Sql聚合函数，也就是⾏转列后计算列的聚合⽅式。
[聚合列值]要进⾏聚合的列名[⾏转列前的列名]这个就是需要将⾏转换为列的列名。
[⾏转列后的列名]这⾥需要声明将⾏的值转换为列后的列名，因为转换后的列名其实就是转换前⾏的值，所以上⾯格式中的[⾏转列后的列名1],[⾏转列后的列名2],[⾏转列后的列名3],......[⾏转列后的列名N]其实就是[⾏转列前的列名]每⼀⾏的值。
下⾯我们来看⼀个例⼦有⼀张表名为[ShoppingCart]有三列[Week],[TotalPrice],[GroupId]，数据和表结构如下所⽰：CREATE TABLE [dbo].[ShoppingCart]([Week] [int] NOT NULL,[TotalPrice] [decimal](18, 0) NOT NULL,[GroupId] [int] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[ShoppingCart] ADD DEFAULT ((0)) FOR [TotalPrice]GOINSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (1, CAST(10 AS Decimal(18, 0)), 1)GOINSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (2, CAST(20 AS Decimal(18, 0)), 1)GOINSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (3, CAST(30 AS Decimal(18, 0)), 1)GOINSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (4, CAST(40 AS Decimal(18, 0)), 1)GOINSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (5, CAST(50 AS Decimal(18, 0)), 1)GOINSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (6, CAST(60 AS Decimal(18, 0)), 1)GOINSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (7, CAST(70 AS Decimal(18, 0)), 1)GO现在我们是⽤PIVOT函数将列[WEEK]的⾏值转换为列，并使⽤聚合函数Count(TotalPrice)来统计每⼀个Week列在转换前有多少⾏数据，语句如下所⽰：select *from ShoppingCart as CPIVOT(count(TotalPrice) FOR [Week] IN([1],[2],[3],[4],[5],[6],[7])) AS T查询结果如下：我们可以看到PIVOT函数成功地将[ShoppingCart]表列[Week]的⾏值转换为了七列，并且每⼀列统计转换前的⾏数为1，这符合我们的预期结果。
sqlserver 中convert的用法
sqlserver 中convert的用法在 SQL Server 中，`CONVERT` 函数用于将一个数据类型的列或值转换为另一个数据类型。
以下是 `CONVERT` 函数的一些常见用法和示例：1. 将列转换为特定数据类型```sqlSELECT CONVERT(int, ColumnName)FROM TableName```2. 将日期转换为特定的格式```sqlSELECT CONVERT(varchar(10), GETDATE(), 101) -- 格式为MM/dd/yyyy```3. 使用样式代码使用样式代码可以在转换时指定日期、时间或数字的格式。
```sqlSELECT CONVERT(varchar, GETDATE(), 103) -- 格式为 dd/mm/yyyy ```常用的样式代码：101: mm/dd/yyyy102:103: dd/mm/yyyy104:105: dd-mm-yyyy常用的数据类型：varcharcharintdatetimesmalldatetime等示例：假设有一个名为 `Employees` 的表，其中包含 `Name` 和 `BirthDate` 列。
要将 `BirthDate` 列转换为 `yyyy-MM-dd` 格式的日期，可以使用以下查询：```sqlSELECT CONVERT(varchar, BirthDate, 120) AS FormattedDateFROM Employees```这将返回 `BirthDate` 列的格式化版本，使用 `yyyy-MM-dd` 格式。
请注意，使用`CONVERT` 函数时，应确保目标数据类型可以容纳源数据类型的数据，以避免数据丢失或转换错误。
使用SQLSERVERPIVOT实现行列转置
使⽤SQLSERVERPIVOT实现⾏列转置⼀般我们在使⽤SQL语句实现⾏列转置时候，最常⽤的⽅法⽆外乎就是 case语句来实现，但是如果需要需要转置的列太多，那么case起来语句就⽆限庞⼤，⼗分不⽅便，sql server中的PIVOT就可以帮助我们解决此类问题PIVOT語法，如下：SELECT <non-pivoted column>,[first pivoted column] AS <column name>,[second pivoted column] AS <column name>,...[last pivoted column] AS <column name>FROM(<SELECT query that produces the data>)AS <alias for the source query>PIVOT(<aggregation function>(<column being aggregated>)FOR[<column that contains the values that will become column headers>]IN ( [first pivoted column], [second pivoted column],... [last pivoted column])) AS <alias for the pivot table><optional ORDER BY clause>;PIVOT語法剖析：PIVOT的語法分三層，⽤三個步驟來使⽤。
第⼀步驟：先把要PIVOT的原始資料查詢(Query)好。
第⼆步驟：設定好PIVOT的欄位與⽅式。
第三步驟：依PIVOT好了的資料，呈現結果。
SELECT <non-pivoted column>, ---- 第三步驟在此，呈現PIVOT後的資料。
SQLServer中几种行列转换的方式
SQLServer中⼏种⾏列转换的⽅式--查询SalesOrder中每年每个⽉各个customer的产⽣的订单总数量1、使⽤PIVOTSELECT x.*FROM ( SELECT YEAR(SalesOrderDate) [Year] ,MONTH(SalesOrderDate) AS [Month] ,CustomerCode ,TotalQTYFROM dbo.SalesOrder) soPIVOT (SUM (so.TotalQTY)FOR [Month] IN ( [1], [2], [3], [4], [5],[6], [7], [8], [9],[10], [11], [12] ))xORDER BY x.[Year] ,x.CustomerCode2、case whenSELECT YEAR(SalesOrderDate) AS Year,CustomerCode,SUM(CASE MONTH(SalesOrderDate)WHEN 1 THEN TotalQTY END) AS '1',SUM(CASE MONTH(SalesOrderDate)WHEN 2 THEN TotalQTY END) AS '2',SUM(CASE MONTH(SalesOrderDate)WHEN 3 THEN TotalQTY END) AS '3',SUM(CASE MONTH(SalesOrderDate)WHEN 4 THEN TotalQTY END) AS '4',SUM(CASE MONTH(SalesOrderDate)WHEN 5 THEN TotalQTY END) AS '5',SUM(CASE MONTH(SalesOrderDate)WHEN 6 THEN TotalQTY END) AS '6',SUM(CASE MONTH(SalesOrderDate)WHEN 7 THEN TotalQTY END) AS '7',SUM(CASE MONTH(SalesOrderDate)WHEN 8 THEN TotalQTY END) AS '8',SUM(CASE MONTH(SalesOrderDate)WHEN 9 THEN TotalQTY END) AS '9',SUM(CASE MONTH(SalesOrderDate)WHEN 10 THEN TotalQTY END) AS '10',SUM(CASE MONTH(SalesOrderDate)WHEN 11 THEN TotalQTY END) AS '11',SUM(CASE MONTH(SalesOrderDate)WHEN 12 THEN TotalQTY END) AS '12'FROM dbo.SalesOrder sGROUP BY YEAR(s.SalesOrderDate),CustomerCodeORDER BY YEAR(SalesOrderDate),CustomerCode3、动态条件DECLARE @PivotColHeader VARCHAR(MAX)SELECT @PivotColHeader =COALESCE(@PivotColHeader + ',[' + cast(MONTH(SalesOrderDate) as varchar) + ']', '[' + cast(MONTH(SalesOrderDate) as varchar) + ']') --⽰例中Name转换为varchar或char类型，注意：在CAST 和CONVERT 中使⽤varchar 时，显⽰n的默认值为30FROM SalesOrderGROUP BY MONTH(SalesOrderDate);DECLARE @PivotTableSQL NVARCHAR(MAX)SET @PivotTableSQL = N'SELECT x.*FROM ( SELECT YEAR(SalesOrderDate) [Year] ,MONTH(SalesOrderDate) AS [Month] ,CustomerCode ,TotalQTYFROM dbo.SalesOrder) soPIVOT (SUM (so.TotalQTY)FOR [Month] IN (''))xORDER BY x.[Year] ,x.CustomerCode'EXECUTE sp_executesql @PivotTableSQL。
SqlServer行转列（PIVOT），列转行（UNPIVOT）总结
SqlServer⾏转列（PIVOT），列转⾏（UNPIVOT）总结PIVOT⽤于将列值旋转为列名（即⾏转列）语法：table_sourcePIVOT(聚合函数（value_column）FOR pivot_columnIN(<column_list>))UNPIVOT⽤于将列明转为列值（即列转⾏）语法：table_sourceUNPIVOT(value_columnFOR pivot_columnIN(<column_list>))注意：PIVOT、UNPIVOT是SQL Server 2005的语法，使⽤需修改数据库兼容级别在数据库属性->选项->兼容级别改为 90⼀、⾏转列1、建⽴表格ifobject_id('tb')isnotnulldroptabletbgoCreate table tb(姓名 varchar(10),课程 varchar(10),分数 int)Insert into tb values('张三','语⽂',74)Insert into tb values('张三','数学',83)Insert into tb values('张三','物理',93)Insert into tb values('李四','语⽂',74)Insert into tb values('李四','数学',84)Insert into tb values('李四','物理',94)goSelect * from tb2、使⽤SQL Server 2000静态SQLselect姓名,Max (case课程when'语⽂'then分数else 0 end)语⽂,Max (case课程when'数学'then分数else 0 end)数学,Max (case课程when'物理'then分数else 0 end)物理From tbGroup by姓名3、使⽤SQL Server 2000动态SQLdeclare@sqlvarchar(500)set@sql='select姓名'select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'from(selectdistinct课程fromtb)a--同from tb group by课程，默认按课程名排序set@sql=@sql+' from tb group by姓名'exec(@sql)--使⽤isnull(),变量先确定动态部分declare@sqlvarchar(8000)select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end) ['+课程+']'from(selectdistinct课程fromtb)asaset@sql='select姓名,'+@sql+' from tb group by姓名'exec(@sql)4、使⽤SQL Server 2005静态SQLselect*fromtb pivot(max(分数)for课程in(语⽂,数学,物理))a5、使⽤SQL Server 2005动态SQL--使⽤stuff()declare@sqlvarchar(8000)set@sql='' --初始化变量@sqlselect@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值set@sql=stuff(@sql,1,1,'')--去掉⾸个','set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'exec(@sql)--或使⽤isnull()declare@sqlvarchar(8000)–-获得课程集合select@sql=isnull(@sql+',','')+课程fromtbgroupby课程set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'exec(@sql)⼆、⾏转列结果加上总分、平均分1、使⽤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))平均分fromtbgroupby姓名2、使⽤SQL Server 2000动态SQL--SQL SERVER 2000动态SQLdeclare@sqlvarchar(500)set@sql='select姓名'select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'from(selectdistinct课程fromtb)aset@sql=@sql+',sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分from tb group by姓名' exec(@sql)3、使⽤SQL Server 2005静态SQLselectm.*,n.总分,n.平均分from(select*fromtb pivot(max(分数)for课程in(语⽂,数学,物理))a)m,(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分fromtbgroupby姓名)nwherem.姓名=n.姓名4、使⽤SQL Server 2005动态SQLdeclare@sqlvarchar(8000)set@sql='' --初始化变量@sqlselect@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值--同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姓名) n where m.姓名= n.姓名'exec(@sql)--或使⽤isnull()declare@sqlvarchar(8000)select@sql=isnull(@sql+',','')+课程fromtbgroupby课程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姓名) n where m.姓名= n.姓名'exec(@sql)⼆、列转⾏1、建⽴表格ifobject_id('tb')isnotnulldroptabletbgocreatetabletb(姓名varchar(10),语⽂int,数学int,物理int)insertintotbvalues('张三',74,83,93)insertintotbvalues('李四',74,84,94)goselect*fromtb2、使⽤SQL Server 2000静态SQL--SQL SERVER 2000静态SQL。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
/*标题：普通行列转换(version 2.0)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：-03-09地点：广东深圳说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
问题：假设有张学生成绩表(tb)如下:姓名课程分数张三语文74张三数学83张三物理93李四语文74李四数学84李四物理94想变成(得到如下结果)：姓名语文数学物理---- ---- ---- ----李四74 84 94张三74 83 93-------------------*/create table tb(姓名varchar(10),课程varchar(10),分数int)insert into tb values('张三','语文', 74)insert into tb values('张三','数学', 83)insert into tb values('张三','物理', 93)insert into tb values('李四','语文', 74)insert into tb values('李四','数学', 84)insert into tb values('李四','物理', 94)go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。
(以下同)select姓名as姓名,max(case课程when'语文'then分数else 0 end)语文,max(case课程when'数学'then分数else 0 end)数学,max(case课程when'物理'then分数else 0 end)物理from tbgroup by姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。
(以下同)declare @sql varchar(8000)set @sql ='select 姓名'select@sql =@sql +' , max(case 课程when '''+课程+''' then 分数else 0 end) ['+课程+']'from(select distinct课程from tb)as aset @sql = @sql +' from tb group by 姓名'exec(@sql)--SQL SERVER 2005 静态SQL。
select*from(select*from tb) a pivot(max(分数)for课程in(语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)select @sql =isnull(@sql +'],[','')+课程from tb group by课程set @sql ='['+ @sql +']'exec('select * from (select * from tb) a pivot (max(分数) for 课程in ('+ @sql +')) b')---------------------------------/*问题：在上述结果的基础上加平均分，总分，得到如下结果：姓名语文数学物理平均分总分---- ---- ---- ---- ------ ----李四74 84 94 84.00 252张三74 83 93 83.33 250*/--SQL SERVER 2000 静态SQL。
select姓名姓名,max(case课程when'语文'then分数else 0 end)语文,max(case课程when'数学'then分数else 0 end)数学,max(case课程when'物理'then分数else 0 end)物理,cast(avg(分数*1.0)as decimal(18,2))平均分,sum(分数)总分from tbgroup by姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)set @sql ='select 姓名'select@sql =@sql +' , max(case 课程when '''+课程+''' then 分数else 0 end) ['+课程+']'from(select distinct课程from tb)as aset @sql = @sql +' , cast(avg(分数*1.0) as decimal(18,2)) 平均分, sum(分数) 总分from tb group by 姓名'exec(@sql)--SQL SERVER 2005 静态SQL。
select m.*, n.平均分, n.总分from(select*from(select*from tb) a pivot(max(分数)for课程in(语文,数学,物理)) b) m,(select姓名,cast(avg(分数*1.0)as decimal(18,2))平均分,sum(分数)总分from tb group by姓名) nwhere m.姓名= n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)select @sql =isnull(@sql +',','')+课程from tb group by课程exec('select m.* , n.平均分, n.总分from(select * from (select * from tb) a pivot (max(分数) for 课程in ('+ @sql + ')) b) m ,(select 姓名, cast(avg(分数*1.0) as decimal(18,2)) 平均分, sum(分数) 总分from tb group by 姓名) nwhere m.姓名= n.姓名')drop table tb------------------------------------/*问题：如果上述两表互相换一下：即表结构和数据为：姓名语文数学物理张三74李四74想变成(得到如下结果)：姓名课程分数---- ---- ----李四语文74李四数学84李四物理94张三语文74张三数学83张三物理93--------------*/create table tb(姓名varchar(10),语文int,数学int,物理int)insert into tb values('张三',74,83,93)insert into tb values('李四',74,84,94)go--SQL SERVER 2000 静态SQL。
select*from(select姓名,课程='语文',分数=语文from tbunion allselect姓名,课程='数学',分数=数学from tbunion allselect姓名,课程='物理',分数=物理from tb) torder by姓名,case课程when'语文'then 1 when'数学'then2 when'物理'then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)select @sql =isnull(@sql +' union all ','')+' select 姓名, [课程] = '+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from tb' from syscolumnswhere name!= N'姓名'and ID =object_id('tb')--表名tb，不包含列名为姓名的其它列order by colid ascexec(@sql +' order by 姓名')--SQL SERVER 2005 动态SQL。
select姓名,课程,分数from tb unpivot(分数for课程in([语文] ,[数学] ,[物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------/*问题：在上述的结果上加个平均分，总分，得到如下结果：姓名课程分数---- ------ ------李四语文 74.00李四数学 84.00李四物理 94.00李四平均分84.00李四总分 252.00张三语文 74.00张三数学 83.00张三物理 93.00张三平均分83.33张三总分 250.00------------------*/select*from(select姓名as姓名,课程='语文',分数=语文from tbunion allselect姓名as姓名,课程='数学',分数=数学from tbunion allselect姓名as姓名,课程='物理',分数=物理from tbunion allselect姓名as姓名,课程='平均分',分数=cast((语文+数学+物理)*1.0/3 as decimal(18,2))from tbunion allselect姓名as姓名,课程='总分',分数=语文+数学+物理from tb) torder by姓名,case课程when'语文'then 1 when'数学'then2 when'物理'then 3 when'平均分'then 4 when'总分'then 5 enddrop table tb。