首页 / MSSQL / SqlServer行转列（PIVOT），列转行（UNPIVOT）总结

SqlServer行转列（PIVOT），列转行（UNPIVOT）总结

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SqlServer行转列（PIVOT），列转行（UNPIVOT）总结，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4424字，纯文字阅读大概需要7分钟。

内容图文

PIVOT用于将列值旋转为列名（即行转列）

语法：

table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(<column_list>)
)

UNPIVOT用于将列明转为列值（即列转行）

语法：

table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)

注意：PIVOT、UNPIVOT是SQL Server 2005的语法，使用需修改数据库兼容级别
在数据库属性->选项->兼容级别改为 90

一、行转列

1、建立表格

ifobject_id(‘tb‘)isnotnulldroptabletb
go
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
Select * from tb

2、使用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)物理
From tb
Group by姓名

3、使用SQL Server 2000动态SQL

declare@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)asa      
set@sql=‘select姓名,‘+@sql+‘ from tb group by姓名‘
exec(@sql)

4、使用SQL Server 2005静态SQL

select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a

5、使用SQL Server 2005动态SQL

--使用stuff()
declare@sqlvarchar(8000)
set@sql=‘‘  --初始化变量@sql
select@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静态SQL

select姓名,
max(case课程when‘语文‘then分数else0end)语文,
max(case课程when‘数学‘then分数else0end)数学,
max(case课程when‘物理‘then分数else0end)物理,
sum(分数)总分,
cast(avg(分数*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名

2、使用SQL Server 2000动态SQL

--SQL SERVER 2000动态SQL
declare@sqlvarchar(500)
set@sql=‘select姓名‘
select@sql=@sql+‘,max(case课程when ‘‘‘+课程+‘‘‘ then分数else 0 end)[‘+课程+‘]‘
from(selectdistinct课程fromtb)a
set@sql=@sql+‘,sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2))      平均分from tb group by姓名‘
exec(@sql)

3、使用SQL Server 2005静态SQL

selectm.*,n.总分,n.平均分
from
(select*fromtb pivot(max(分数)for课程in(语文,数学,物理))a)m,
(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名)n
wherem.姓名=n.姓名

4、使用SQL Server 2005动态SQL

declare@sqlvarchar(8000)
set@sql=‘‘  --初始化变量@sql
select@sql=@sql+‘,‘+课程fromtbgroupby课程--变量多值赋值
--同select @sql = @sql + ‘,‘+课程from (select distinct课程from tb)a
set@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‘)isnotnulldroptabletb
go
createtabletb(姓名varchar(10),语文int,数学int,物理int)
insertintotbvalues(‘张三‘,74,83,93)
insertintotbvalues(‘李四‘,74,84,94)
go
select*fromtb

2、使用SQL Server 2000静态SQL

--SQL SERVER 2000静态SQL。
select*from
(
 select姓名,课程=‘语文‘,分数=语文fromtb
 unionall
 select姓名,课程=‘数学‘,分数=数学fromtb
 unionall
 select姓名,课程=‘物理‘,分数=物理fromtb
) t
orderby姓名,case课程when‘语文‘then1when‘数学‘then2when‘物理‘then3end

2、使用SQL Server 2000动态SQL

--SQL SERVER 2000动态SQL。
--调用系统表动态生态。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+‘ union all ‘,‘‘)+‘ select姓名, [课程]=‘
+quotename(Name,‘‘‘‘)+‘ , [分数] = ‘+quotename(Name)+‘ from tb‘
fromsyscolumns
whereName!=‘姓名‘andID=object_id(‘tb‘)--表名tb，不包含列名为姓名的其他列
orderbycolid
exec(@sql+‘ order by姓名‘)

3、使用SQL Server 2005静态SQL 　　

--SQL SERVER 2005动态SQL
select姓名,课程,分数fromtb unpivot (分数for课程in([语文],[数学],[物理])) t

4、使用SQL Server 2005动态SQL

--SQL SERVER 2005动态SQL
declare@sqlnvarchar(4000)
select@sql=isnull(@sql+‘,‘,‘‘)+quotename(Name)
fromsyscolumns
whereID=object_id(‘tb‘)andNamenotin(‘姓名‘)
orderbyColid
set@sql=‘select姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in(‘+@sql+‘))b‘
exec(@sql)

原文：http://www.cnblogs.com/sunliyuan/p/7044608.html

内容总结

以上是互联网集市为您收集整理的SqlServer行转列（PIVOT），列转行（UNPIVOT）总结全部内容，希望文章能够帮你解决SqlServer行转列（PIVOT），列转行（UNPIVOT）总结所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1252994.html

来源：【匿名】

【上一篇】SQL Server安全（11/11）：审核（Auditing）【下一篇】一个odbc连mssql分页的类

更多 ►

【SqlServer行转列（PIVOT），列转行（UNPIVOT）总结】教程文章相关的互联网学习教程文章

SQL Server中使用PIVOT行转列【代码】【图】

1.建表及插入数据 1USE[AdventureDB] 2GO 3/****** Object: Table [dbo].[Score] Script Date: 11/25/2016 4:30:50 PM ******/ 4SET ANSI_NULLS ON 5GO 6 7SET QUOTED_IDENTIFIER ON 8GO 910CREATETABLE[dbo].[Score]([Name][varchar](50) NULL,[Subject][varchar](50) NULL,[Score]FLOATNULL) ON[PRIMARY]11GO1213INSERTINTO[dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Jack‘, N‘linguistic‘, 65) 14INSERTINT...

sqlserver 行转列【代码】

第一种SELECT title,SUM(CASE typeid WHEN ‘1‘ THEN gjdate ELSE 0 END) AS ‘调休‘,SUM(CASE typeid WHEN ‘2‘ THEN gjdate ELSE 0 END) AS ‘请假‘,SUM(CASE typeid WHEN ‘3‘ THEN gjdate ELSE 0 END) AS ‘加班‘ FROM lfil_tbGROUP BY title第二种DECLARE @sql_str NVARCHAR(MAX) DECLARE @sql_col NVARCHAR(MAX) DECLARE @tableName SYSNAME --表名 DECLARE @groupColumn SYSNAME --分组字段(title name 什么玩意...

SQL Server 动态行转列(轉載)【图】

一.本文所涉及的内容(Contents)本文所涉及的内容(Contents)背景(Contexts)实现代码(SQL Codes)方法一：使用拼接SQL，静态列字段;方法二：使用拼接SQL，动态列字段;方法三：使用PIVOT关系运算符，静态列字段;方法四：使用PIVOT关系运算符，动态列字段;二.背景(Contexts)其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基...

sqlserver 行转列、列转行[转]【代码】【图】

创建表： CREATE TABLE [dbo].[tbl_Student]( [ID] [int] IDENTITY(1,1) NOT NULL, [学生ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [学生姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [课程ID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, [课程名称] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [成绩] [int] NULL, [教师ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, ...

转：SQL Server 动态行转列

http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html转：SQL Server 动态行转列标签：本文系统来源：http://www.cnblogs.com/zuiyirenjian/p/4508026.html

做图表统计你需要掌握SQL Server 行转列和列转行【代码】【图】

FOR pivot_columnIN(<column_list>)) 1.2 行转列案例WITH T AS (SELECT 1 as TeamId,‘测试团队1‘ as Team,80 ‘MEN‘,20 ‘WOMEN‘UNION SELECT 2 as TeamId,‘测试团队2‘ as Team,30 ‘MEN‘,70 ‘WOMEN‘ )---列转行------------------------------------ SELECT TeamId,Team ,TYPE=ATTRIBUTE,CNT=VALUE FROM T UNPIVOT (VALUE FOR ATTRIBUTE IN ([MEN],[WOMEN]) ) AS UPV 2、行转列行转列主要是从中间表里查询数...

SQLServer处理行转列和列转行【代码】【图】

table_sourceUNPIVOT(value_columnFOR pivot_columnIN(<column_list>)) 1.2 行转列案例 WITH T AS (SELECT 1 as TeamId,‘测试团队1‘ as Team,80 ‘MEN‘,20 ‘WOMEN‘UNION SELECT 2 as TeamId,‘测试团队2‘ as Team,30 ‘MEN‘,70 ‘WOMEN‘ )---列转行------------------------------------ SELECT TeamId,Team ,TYPE=ATTRIBUTE,CNT=VALUE FROM T UNPIVOT (VALUE FOR ATTRIBUTE IN ([MEN],[WOMEN]) ) AS UPV 2、行转列...

SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）【图】

SQLserver行转列与列转行

================================================================== ----------------------------行转列---------------------------------- --================================================================== -------------------------建立测试表 if exists(select * from sysobjects where ID=OBJECT_ID(N‘BodyInfo‘) AND XTYPE=‘U‘) DROP TABLE BodyInfo Create Table BodyInfo (姓名 varchar(20),属性 ...

SQL Server 行转列

PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法： table_source PIVOT( 聚合函数（value_column） FOR pivot_column IN(<column_list>) ) UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现完整语法： table_source UNPIVOT( value_column FOR pivot_column IN(<column_list>) ) 注...

SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）【代码】【图】

一.本文所涉及的内容（Contents）本文所涉及的内容（Contents）背景（Contexts）实现代码（SQL Codes）方法一：使用拼接SQL，静态列字段；方法二：使用拼接SQL，动态列字段；方法三：使用PIVOT关系运算符，静态列字段；方法四：使用PIVOT关系运算符，动态列字段；扩展阅读一：参数化表名、分组列、行转列字段、字段值；扩展阅读二：在前面的基础上加入条件过滤；参考文献（References）二.背景（Contexts）其实行转列并不是...

SQL Server中使用PIVOT行转列【代码】【图】

1 USE [AdventureDB]2 GO3 /****** Object: Table [dbo].[Score] Script Date: 11/25/2016 4:30:50 PM ******/4 SET ANSI_NULLS ON5 GO6 7 SET QUOTED_IDENTIFIER ON8 GO9 10 CREATE TABLE [dbo].[Score]([Name] [varchar](50) NULL,[Subject] [varchar](50) NULL,[Score] FLOAT NULL) ON [PRIMARY] 11 GO 12 13 INSERT INTO [dbo].[Score] ([Name], [Subject], [Score]) VALUES (N‘Jack‘, N‘linguistic‘, 65) 14 INSERT...

sqlserver 行转列【代码】【图】

1 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示： --创建测试表 CREATE TABLE [dbo].[TestRows2Columns]([Id] [int] IDENTITY(1,1) NOT NULL,[UserName] [nvarchar](50) NULL,[Subject] [nvarchar](50) NULL,[Source] [numeric](18,0) NULL ) GO--插入测试数据 INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source]) SELECT N‘张三‘,N‘语文‘,60 UNION ALLSELECT N‘李四‘,N‘数学‘,70 U...

SqlServer 行转列

SELECT 列名+‘,‘ FROM 表名 FOR XML PATH(‘‘)SqlServer 行转列标签：server string for sel code 行转列 sql sqlserver nbsp 本文系统来源：http://www.cnblogs.com/licin/p/6490822.html

Sqlserver中PIVOT行转列透视操作【代码】【图】

创建表：IF OBJECT_ID(‘T040_PRODUCT_SALES‘) IS NOT NULL DROP TABLE T040_PRODUCT_SALES create table T040_PRODUCT_SALES (ID INT IDENTITY(1,1),ProductName VARCHAR(20),SaleMonth INT,SalesCount INT ) 插入数据并排序：INSERT INTO T040_PRODUCT_SALES VALUES (‘Bicycle‘,1,1), (‘Shoes‘,2,2), (‘Clothes‘,3,3), (‘Books‘,4,4), (‘Medicine‘,5,5), (‘Drinks‘,6,6), (‘Shoes‘,7,7), (‘Books‘,1,2), (‘Bi...

MSSQL - 最热教程

sqlServer 查询特定的表（字段或者文字...sqlserver建表及注释 sql server 2008出现远程过程调用失败 sql server导出到excel错误：未在本地计...Window Server2019安装SqlServer2012管...SQLserver使用truncate语句报错：找不...SQL Server 2008 下载及安装教程 SQL SERVER将多行数据合并成一行（转）SQL SERVER 中设置字段默认值为当前时间 php Sql Server连接失败问题及解决办法

首页 / MSSQL / SqlServer行转列（PIVOT），列转行（UNPIVOT）总结

SqlServer行转列（PIVOT），列转行（UNPIVOT）总结

内容导读

内容图文

内容总结

内容备注

内容手机端

【SqlServer行转列（PIVOT），列转行（UNPIVOT）总结】教程文章相关的互联网学习教程文章

MSSQL - 最新教程

MSSQL - 最热教程