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。

SqlServer数据库怎么实现行转列的sql语句

SqlServer数据库怎么实现行转列的sql语句？问题描述假设有张学生成绩表(CJ)如下Name Subject Result张三语文 80张三数学 90张三物理 85李四语文 85李四数学 92李四物理 82现在想写 sql 语句查询后结果为姓名语文数学物理张三 80 90 85李四 85 92 82 该怎么实现？研究意义这是个并不复杂的问题，但却是数据库中行转列的一个典型例子，只要把这个抽象出来的具有普遍意义的问题研究透彻，其他类似的复杂问题迎刃而解。

问题分析首先介绍下行转列的概念，也许书上并没有这个概念，行转列说的是这样一类问题：有时候为了数据库表的设计满足用户的动态要求（比如添加字段），我们采用定义字段名表，然后定义一个字段值的表，这样就达到了用静态来表达动态，换句话说就是把数据库表中本来应该是横向的延伸转化为纵向的延伸，再换句话说就是把数据库表中本来应该是字段的增加转化为记录条数的增加。

然而，在这样设计下，固然灵活，确带来了统计分析的麻烦，因为统计分析时，应该是以直观的形式进行表现。

换言之，统计分析时，我们又应该显示为字段更多的那种。

如果同时做到了数据存储时列的增加转化为行的增加，数据提取时又可得到列增加了的数据，数据库表的这种设计就对用户透明了。

本文前面提出的这个问题就是一个典型的在数据提取时要把以行增加形式的数据转化为以列增加形式的数据。

为什么这样说呢？我们注意subject字段，subject里的内容在数据库存储时是以不同数据行的形式，换言之，是以行增加的形式，而输出时，这里面的内容我们要变成字段名了。

衡量这个问题解决好坏我们有几个标准：1.当数据正好就是上面这个样子时，解决办法能否得到正确的解；2.如果增加科目了科目的种类，解决方法是否仍然能行得通；3.如果有些人的某们课程的成绩还没有下来，换言之，数据库中不是每个人每门课的成绩都可以找到，数据库缺少某个人某门课的成绩的记录。

sql server行转列查询语句

sql server行转列查询语句SQL Server行转列查询语句是一种非常有用的查询技巧，可以将一行的数据转换为多列的形式，这对于某些业务场景非常实用。

本文将详细介绍SQL Server行转列查询语句的使用方法和注意事项，希望能帮助读者更好地掌握这一技巧。

一、什么是行转列查询语句行转列查询语句是一种将一行数据转换为多列数据的查询技巧。

在数据库中，数据通常以行的形式存储，每一行代表一个记录。

但是在某些场景下，我们需要将行数据转换为列数据，以便更好地展示和处理数据。

SQL Server提供了一些函数和关键字来实现行转列查询。

二、行转列查询的应用场景行转列查询常见的应用场景包括以下几种：1. 交叉表查询：将某一列的值作为新列，将另一列的值作为新行，用于统计和分析数据。

2. 报表生成：将多行数据按照某个字段进行分组，转换为多列数据，方便生成报表。

3. 数据展示：将一张表中的多行数据转换为一行数据，用于展示和查询。

三、行转列查询的基本语法SQL Server中实现行转列查询通常使用PIVOT和UNPIVOT关键字。

下面是行转列查询的基本语法：1. PIVOT语法：SELECT [列1], [列2], ...FROM [表名]PIVOT(聚合函数([列名])FOR [列名] IN ([列值1], [列值2], ...)) AS [别名]2. UNPIVOT语法：SELECT [列名], [值]FROM [表名]UNPIVOT([值] FOR [列名] IN ([列1], [列2], ...)) AS [别名]需要注意的是，PIVOT语句中的聚合函数可以是SUM、COUNT、AVG 等，适用于需要对数据进行统计的场景。

四、行转列查询的实际案例为了更好地理解行转列查询，下面以一个实际案例来演示如何使用行转列查询语句。

我们有一个销售数据表sales_data，包含以下列：产品名称（product_name）、区域（region）、销售额（sales_amount）。

sqlserver行列转换（行传换为列）

sqlserver⾏列转换（⾏传换为列）1.原始查询SELECT AS ShiftName,h.BusinessEntityID, AS DepartmentNameFROM HumanResources.EmployeeDepartmentHistory hINNER JOIN HumanResources.Department dON h.DepartmentID = d.DepartmentIDINNER JOIN HumanResources.Shift sON h.ShiftID = s.ShiftIDWHERE EndDate IS NULLAND IN ('Production', 'Engineering', 'Marketing')ORDER BY ShiftName;结果如下在这个结果集中，我们可以看到所有的部门都列在⼀个列中。

下⼀步是将此查询返回的部门值转换为列，以及按班次计算的员⼯数。

想要实现的效果2.⽤PIVOT关键字进⾏⾏列转换1SELECT ShiftName,2 Production,3 Engineering,4 Marketing5FROM (SELECT AS ShiftName,6 h.BusinessEntityID,7 AS DepartmentName8FROM HumanResources.EmployeeDepartmentHistory h9INNER JOIN HumanResources.Department d10ON h.DepartmentID = d.DepartmentID11INNER JOIN HumanResources.Shift s12ON h.ShiftID = s.ShiftID13WHERE EndDate IS NULL14AND IN ('Production', 'Engineering', 'Marketing')15 ) AS a16 PIVOT17 (18COUNT(BusinessEntityID)19FOR DepartmentName IN ([Production], [Engineering], [Marketing])20 ) AS b21ORDER BY ShiftName;结果如下:3.转换的语法如下:4.参数的定义如下:官⽹⽰义5.在引⼊PIVOT操作符之前,我们是通过以下语句进⾏转换的1SELECT AS ShiftName,2SUM(CASE WHEN ='Production'THEN1ELSE0END) AS Production,3SUM(CASE WHEN ='Engineering'THEN1ELSE0END) AS Engineering,4SUM(CASE WHEN ='Marketing'THEN1ELSE0END) AS Marketing5FROM HumanResources.EmployeeDepartmentHistory h6INNER JOIN HumanResources.Department d7ON h.DepartmentID = d.DepartmentID8INNER JOIN HumanResources.Shift s9ON h.ShiftID = s.ShiftID10WHERE h.EndDate IS NULL11AND IN ('Production', 'Engineering', 'Marketing')12GROUP BY ;也是同样结果6.这两种⽅法⽆论是⽤PIVOT关键字还是直接聚合运算都需要事先知道部门的名称,因为必须要有列名,这种情况可以先查询⼀下部门,然后动态拼接进column_list ,但是这种动态拼接似乎⽤第⼆种⽅式⽆法实现。

SQLserver行转列列转行

SQLserver⾏转列列转⾏1.简单案例create table student(sid int primary key identity(1,1), --主键⾃增sName varchar(20), --学⽣姓名)select * from studentcreate table class(cid int primary key identity(1,1), --主键⾃增cName varchar(20))select* from classcreate table score(scid int primary key identity(1,1), --主键⾃增scName int,sid int,cid int)select * from scoreselect * from(select a.sName,b.scName,ame from student as ainner join score as b on a.[sid]=b.[sid]inner join class as c on b.cid=c.cid) as Ppivot(sum(P.scName) forame in (语⽂,数学,英语)) as T2.另⼀案例select Name as ⽔果,max(case RegionName when '北京' then Price else 0 end) 北京,max(case RegionName when '⼴州' then Price else 0 end) ⼴州from (select ,r.RegionName,rf.Price from Fruits fjoin RegionPrice rf on f.ID =rf.FruitIDjoin Regions r on rf.RegionID =r.id) tb group by Nameselect ,r.RegionName,rf.Price from Fruits fjoin RegionPrice rf on f.ID =rf.FruitIDjoin Regions r on rf.RegionID =r.idselect Name as ⽔果,case RegionName when '北京' then Price else 0 end 北京,case RegionName when '⼴州' then Price else 0 end ⼴州from (select ,r.RegionName,rf.Price from Fruits fjoin RegionPrice rf on f.ID =rf.FruitIDjoin Regions r on rf.RegionID =r.id) tbselect * from(select ,r.RegionName,rf.Price from Fruits fjoin RegionPrice rf on f.ID =rf.FruitIDjoin Regions r on rf.RegionID =r.id) tbpivot(max(tb.Price) for tb.RegionName in([⼴州],[北京])) as a。

sqlserver（PIVOT）行列转换

sqlserver（PIVOT）⾏列转换原理：利⽤#Temp临时表 + sp_executesql ⽂本sql的⽅式。

解决完全⽤⽂本SQL实现所有SQL 的问题，那样代码不好检查错误和编写。

最近项⽬中⽤到Pivot 对表进⾏转置，遇到⼀些问题，主要是Pivot 转置的时候没有办法动态产⽣转置列名,⽽作视图的时候⼜很需要动态的产⽣这些列，百度上似乎也没有找的很满意的答案，在google上搜到⼀⽼外的解决⽅案，现在⾃⼰总结了⼀下，希望给⽤的上的朋友⼀些帮助。

1.创建表脚本if exists (select 1from sysobjectswhere id = object_id('Insurances')and type = 'U')drop table Insurancesgo/*==============================================================*//* Table: Insurances *//*==============================================================*/create table Insurances (RefID uniqueidentifier not null,HRMS nvarchar(20) null,Name nvarchar(20) null,InsuranceMoney money null,InsuranceName nvarchar(100) not null,constraint PK_INSURANCES primary key (RefID))go2.测试数据脚本insert into Insurances values (newid(),1,'张三',200,'养⽼保险')insert into Insurances values (newid(),1,'张三',300,'医疗保险')insert into Insurances values (newid(),2,'李四',250,'养⽼保险')insert into Insurances values (newid(),2,'李四',350,'医疗保险')insert into Insurances values (newid(),3,'王⼆',150,'养⽼保险')insert into Insurances values (newid(),3,'王⼆',300,'医疗保险')3.查询表数据select HRMS,Name,InsuranceMoney,InsuranceName From InsurancesHRMS Name InsuranceMoney InsuranceName -------------------- -------------------- --------------------- ----------1 张三 200.00 养⽼保险2 李四 350.00 医疗保险2 李四 250.00 养⽼保险1 张三 300.00 医疗保险3 王⼆ 300.00 医疗保险3 王⼆ 150.00 养⽼保险4.转置表数据select * from(select HRMS,Name,InsuranceMoney,InsuranceName from Insurances ) pPivot (sum(InsuranceMoney)FOR InsuranceName IN( [医疗保险], [养⽼保险]))as pvtHRMS Name 医疗保险养⽼保险-------------------- -------------------- --------------------- ---------------------2 李四 350.00 250.003 王⼆ 300.00 150.001 张三 300.00 200.005.偶的问题这个语句中医疗保险、养⽼保险是SQL语句中写死的，⽽且Sql2005中这个代码没有办法使⽤动态的查询结果集5.存储过程解决问题所以如果要动态的完成个脚本，可以先拼出SQL 然后通过exec sp_executesql 执⾏实现存储过程create procedure InsurancePivotasBeginDECLARE @ColumnNames VARCHAR(3000)SET @ColumnNames=''SELECT@ColumnNames = @ColumnNames + '[' + InsuranceName + '],'FROM(SELECT DISTINCT InsuranceName FROM Insurances) tSET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1)DECLARE @selectSQL NVARCHAR(3000)SET @selectSQL='SELECT HRMS,Name,{0} FROM(SELECT HRMS,Name,InsuranceMoney,InsuranceName FROM Insurances ) pPivot( Max(InsuranceMoney) For InsuranceName in ({0})) AS pvtORDER BY HRMS'SET @selectSQL= REPLACE(@selectSQL,'{0}',@ColumnNames)exec sp_executesql @selectSQLend测试存储过程：exec InsurancePivotHRMS Name 养⽼保险医疗保险-------------------- -------------------- --------------------- ---------------------1 张三 200.00 300.002 李四 250.00 350.003 王⼆ 150.00 300.00。

行转列之SQLSERVERPIVOT与用法详解

⾏转列之SQLSERVERPIVOT与⽤法详解在数据库操作中，有些时候我们遇到需要实现“⾏转列”的需求，例如⼀下的表为某店铺的⼀周收⼊情况表：WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)我们先插⼊⼀些模拟数据：INSERT INTO WEEK_INCOMESELECT '星期⼀',1000UNION ALLSELECT '星期⼆',2000UNION ALLSELECT '星期三',3000UNION ALLSELECT '星期四',4000UNION ALLSELECT '星期五',5000UNION ALLSELECT '星期六',6000UNION ALLSELECT '星期⽇',7000⼀般我们最经常使⽤的查询是查询⼀周中每天或某⼏天的收⼊，例如查询周⼀⾄周⽇全部的收⼊：SELECT WEEK,INCOME FROM WEEK_INCOME得到如下的查询结果集：WEEK INCOME星期⼀ 1000星期⼆ 2000星期三 3000星期四 4000星期五 5000星期六 6000星期⽇ 7000但是在⼀些情况下（往往是某些报表中），我们希望在⼀⾏中显⽰周⼀⾄周⽇的收⼊，这时候查询结果集应该是这样的：星期⼀星期⼆星期三星期四星期五星期六星期⽇1000 2000 3000 4000 5000 6000 7000这种情况下，SQL查询语句可以这样写：SELECTSUM(CASE WEEK WHEN '星期⼀' THEN INCOME END) AS [星期⼀],SUM(CASE WEEK WHEN '星期⼆' THEN INCOME END) AS [星期⼆],SUM(CASE WEEK WHEN '星期三' THEN INCOME END) AS [星期三],SUM(CASE WEEK WHEN '星期四' THEN INCOME END) AS [星期四],SUM(CASE WEEK WHEN '星期五' THEN INCOME END) AS [星期五],SUM(CASE WEEK WHEN '星期六' THEN INCOME END) AS [星期六],SUM(CASE WEEK WHEN '星期⽇' THEN INCOME END) AS [星期⽇]FROM WEEK_INCOME但是，在SQL SERVER 2005中提供了更为简便的⽅法，这就是"PIVOT"关系运算符。

sqlserver行转列函数

sqlserver行转列函数
SQLServer行转列是一种在SQLServer中经常用到的转换数据的方式，它能够从原始的行数据中提取出多个列的数据，这样可以帮助用户有效地
查看数据，从而更好地进行分析和决策。

SQLServer中行转列一般有以下
三种方法：
1、使用PIVOT操作，PIVOT操作可以帮助把行转变成列，有效地更
改数据的视图，是行转列中使用最多的操作之一。

2、使用UNPIVOT操作，与PIVOT操作相反，UNPIVOT操作可以将原
始的列通过一定的处理转换为行。

3、使用With clause,With clause 可以同时兼容PIVOT和UNPIVOT
两种方法，实现行列互转，并且With clause支持行转列函数的多态化。

总的来说，SQLServer行转列的技巧非常多，无论是使用PIVOT操作，UNPIVOT操作，还是使用With clause，都可以有效地实现SQLServer中
行转列的目的，从而帮助更好地处理和分析数据，提升对数据深入分析和
理解的能力。

sqlserver行列转换

sqlserver⾏列转换⼀、创建测试数据--创建测试表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'语⽂',60 UNION ALLSELECT N'李四',N'数学',70 UNION ALLSELECT N'王五',N'英语',80 UNION ALLSELECT N'王五',N'数学',75 UNION ALLSELECT N'王五',N'语⽂',57 UNION ALLSELECT N'李四',N'语⽂',80 UNION ALLSELECT N'张三',N'英语',100GO⼆、⾏转列--⾏转列SELECT[UserName],[语⽂],[数学],[英语]FROM[TestRows2Columns]PIVOT(SUM([Source]) for[Subject]in([语⽂],[数学],[英语]))TBL三、多⾏查询合并到⼀⾏--多⾏转⼀⾏SELECT [UserName],[val]=STUFF( (SELECT ','+[Subject]FROM [TestRows2Columns] AS secordTableWHERE secordTable.[UserName] = firstTable.[UserName]FOR XML PATH('')) , 1 , 1 , '' )FROM [TestRows2Columns] AS firstTableGROUP BY [UserName]四、列转⾏测试数据--列转⾏CREATE TABLE Column2Line([⼯程名称] NVARCHAR(20), --⼯程名称海外供应商供给数量 INT, --海外供应商供给数量国内供应商供给数量 INT, --国内供应商供给数量南⽅供应商供给数量 INT, --南⽅供应商供给数量北⽅供应商供给数量 INT --北⽅供应商供给数量)INSERT INTO Column2LineSELECT 'A', 100, 200, 50, 50UNION ALLSELECT 'B', 200, 300, 150, 150UNION ALLSELECT 'C', 159, 400, 20, 320UNION ALLSELECT 'D',250,30,15,15列转⾏SELECT P.[⼯程名称],P.供应商类型,P.销量FROM(SELECT [⼯程名称], 海外供应商供给数量, 国内供应商供给数量,南⽅供应商供给数量, 北⽅供应商供给数量FROM Column2Line)TUNPIVOT(销量 FOR 供应商类型 IN(海外供应商供给数量, 国内供应商供给数量, 南⽅供应商供给数量, 北⽅供应商供给数量 ) ) P;。

sql的行转列（casewhen）sqlserver.net

sql的⾏转列（casewhen）⾏转列之前SQLSELECT zhenshi, status, COUNT(1) AS nFROM [MyPlan]WHERE 1 = 1AND leibie = 1AND [SickID] = 0AND zhenshi <> '协同诊疗'AND t >= '2020-06-06 00:00:00'AND t < '2020-06-08 00:00:00'GROUP BY zhenshi, status⾏转列之前的运⾏结果------------------------------------------------------------------------------------------------------⾏转列之后SQLselect 0,zhenshi,sum(case --statuswhen status!='完成' then nelse 0end) '预约病⼈数量',sum(case statuswhen '已来诊' then nelse 0end) '已来诊',sum(case --statuswhen status!='已来诊' and status!='完成' then nelse 0end) '未来诊'from(select zhenshi,status,count(1) n--CASE status WHEN '开启' THEN status ELSE '开启' END,--CASE status WHEN '已来诊' THEN status ELSE '已来诊' END,--CASE status WHEN '未来诊' THEN status ELSE '未来诊' ENDFROM [MyPlan] where 1=1 and leibie=1 and [SickID] =0 and zhenshi<>'协同诊疗' --and status='已来诊'--and zhenshi='好孕⼀组'and t>='2020-06-06 00:00:00' and t<'2020-06-08 00:00:00'group by zhenshi,status)twhere t.zhenshi!=''group by zhenshi⾏转列之后的运⾏结果这时候再去程序⾥遍历，就简单显⽰了。

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

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。