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⾏转列--创建⾏转列表及插⼊数据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⽰例。

SQLSERVER行列转换（动态）

SQLSERVER⾏列转换（动态）⾏转列测试数据：--测试数据if not object_id(N'Tempdb..#T') is nulldrop table #TGoCreate table #T([Name] nvarchar(22),[Subject] nvarchar(22),[Score] int)Insert #Tselect N'李四',N'语⽂',60 union allselect N'李四',N'数学',70 union allselect N'李四',N'英语',80 union allselect N'张三',N'语⽂',90 union allselect N'张三',N'数学',80 union allselect N'张三',N'英语',70Go--测试数据结束动态写法（加上了总分和平均分）：DECLARE @sql VARCHAR(MAX)SET @sql = 'select Name'SELECT @sql = @sql + ',max(case Subject when ''' + Subject+ ''' then Score else 0 end)[' + Subject + ']'FROM ( SELECT DISTINCTSubjectFROM #T) aSET @sql = @sql+ ',sum(Score) 总分,cast(avg(Score*1.0) as decimal(18,2)) 平均分 from #T group by Name'EXEC(@sql)动态的也可以使⽤pivot：DECLARE @sql VARCHAR(MAX)SELECT @sql=isnull(@sql+',','')+Subject FROM #T GROUP BY SubjectSET @sql='select m.* , n.总分, n.平均分 from(select * from (select * from #T) a pivot (max(Score) for Subject in ('+@sql+')) b) m ,(select Name,sum(Score)总分, cast(avg(Score*1.0) as decimal(18,2))平均分 from #T group by Name) nwhere = 'exec(@sql)列转⾏的测试数据：--测试数据if not object_id(N'Tt') is nulldrop table TtGoCreate table Tt([姓名] nvarchar(22),[语⽂] int,[数学] int,[英语] int)Insert Ttselect N'张三',60,70,80 union allselect N'李四',90,80,70Go--测试数据结束动态写法：DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+' union all ','')+' select 姓名, [课程]='+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from T'FROM syscolumnsWHERE Name!='姓名' AND ID=object_id('T')--表名tb，不包含列名为姓名的其他列ORDER BY colidEXEC(@sql+' order by 姓名')同样的动态写法也可以使⽤unpivot：DECLARE @sql VARCHAR(8000)SELECT @sql=isnull(@sql+',','')+quotename(Name)FROM syscolumnsWHERE ID=object_id('T')AND Name NOT IN('姓名')ORDER BY ColidSET @sql='select 姓名,[课程],[分数] from T unpivot ([分数] for [课程] in('+@sql+'))b'exec(@sql)。

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行转列函数
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⾏列转换我们在写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，这符合我们的预期结果。

使用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後的資料。

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中⾏列转置⽅法PIVOT⽤于将列值旋转为列名（即⾏转列），在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、建⽴表格ifobject_id('tb')isnotnulldroptabletbgocreatetabletb(姓名varchar(10),课程varchar(10),分数int)insertintotbvalues('张三','语⽂',74)insertintotbvalues('张三','数学',83)insertintotbvalues('张三','物理',93)insertintotbvalues('李四','语⽂',74)insertintotbvalues('李四','数学',84)insertintotbvalues('李四','物理',94)goselect*fromtbgo姓名课程分数---------- ---------- -----------张三语⽂ 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)物理fromtbgroupby姓名姓名语⽂数学物理---------- ----------- ----------- -----------李四 74 84 94张三 74 83 933、使⽤SQL Server 2000动态SQL--SQL SERVER 2000动态SQL,指课程不⽌语⽂、数学、物理这三门课程。

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行列转换

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;。

SQLServer使用STUFF-forxmlpath实现结果行列转置

看一下结果：
场景2：将情景1的favor结果，变成中文。这需要先拆了，再拼接一次。
demo表：这次需要将favor通过tb_favorCname表，替换成中文
sql语句：
select cname 姓名,stuff((select '、'+ame from tb_favorCname
fc
while @i_c <> 0
begin
set @ch = substring(@str, @i_p + 1, @i_c - @i_p - 1); INSERT @temp VALUES (@ch); set @i_p = @i_c; set @i_c = CHARINDEX(@split, @str, @i_c + 1); end
forxmlpath这句是把得到的内容以xml的形式显示
SQLServer使用STUFFቤተ መጻሕፍቲ ባይዱforxmlpath实现结果行列转置
源数据：
场景1：查出用户的爱好，并进行行列转置
select cname,stuff((select ','+f.favor from tb_favor f where erid=
,dbo.FnSplitStr((select favor from tb_demo1 d where ername=
ername
), ',') tt where fc.favor=
tt.F1
for xml path('')),1,1,'') 兴趣from tb_user bb
结果：
erid
FOR XML Path('')),1,1,'') favorfrom tb_user

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（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。