SQL Server 行转列

最新推荐文章于 2023-04-18 17:57:32 发布
最新推荐文章于 2023-04-18 17:57:32 发布
阅读量951 收藏
点赞数
分类专栏： 备忘录 文章标签： sql server 行转列 行转列

PIVOT 用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现

PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

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

SQL2008 中可以直接使用

完整语法：

复制代码 
table_source

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN(<column_list>)

)
复制代码
View Code

UNPIVOT 用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现

复制代码 
完整语法：

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)
复制代码

 

典型实例

一、行转列

1、建立表格

复制代码 
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb

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

go
复制代码

姓名       课程       分数

---------- ---------- -----------

张三       语文        74

张三       数学        83

张三       物理        93

李四       语文        74

李四       数学        84

李四       物理        94

 

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

复制代码 
--SQL SERVER 2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

--变量按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 * FROM tb pivot( MAX(分数) FOR 课程 IN (语文,数学,物理))a

姓名       语文        数学        物理

---------- ----------- ----------- -----------

李四        74          84          94

张三        74          83          93

5、使用SQL Server 2005动态SQL

复制代码 
--使用stuff()

DECLARE @sql VARCHAR(8000)

SET @sql=''  --初始化变量 @sql

SELECT @sql= @sql+',' + 课程 FROM tb GROUP BY 课程 --变量多值赋值

SET @sql= STUFF(@sql,1,1,'')--去掉首个','

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

PRINT @sql

exec(@sql)

--或使用isnull()

DECLARE @sql VARCHAR(8000)

--获得课程集合

SELECT @sql= ISNULL(@sql+',','')+课程 FROM tb
GROUP BY 课程           

SET @sql='select * from tb pivot (max(分数) for 课程 in ('+@sql+'))a'

exec(@sql)
复制代码

二、行转列结果加上总分、平均分

1、使用SQL Server 2000静态SQL

复制代码 
--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姓名
复制代码

姓名       语文        数学        物理        总分        平均分

---------- ----------- ----------- ----------- -----------

李四        74          84          94          252         84.00

张三        74          83          93          250         83.33

 

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

复制代码 
SELECT  m.* ,
        n.总分 ,
        n.平均分
FROM    ( SELECT    *
          FROM      tb PIVOT( MAX(分数) FOR 课程 IN ( 语文, 数学, 物理 ) ) a
        ) m ,
        ( SELECT    姓名 ,
                    SUM(分数) 总分 ,
                    CAST(AVG(分数 * 1.0) AS DECIMAL(18, 2)) 平均分
          FROM      tb
          GROUP BY  姓名
        ) n
WHERE   m.姓名 = n.姓名
复制代码

4、使用SQL Server 2005动态SQL

复制代码 
--使用stuff()
DECLARE @sql VARCHAR(8000)

SET @sql = ''
  --初始化变量@sql

SELECT  @sql = @sql + ',' + 课程
FROM    tb
GROUP BY 课程
--变量多值赋值

--同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 @sql VARCHAR(8000)

SELECT  @sql = ISNULL(@sql + ',', '') + 课程
FROM    tb
GROUP BY 课程

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、建立表格

复制代码 
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
go

CREATE TABLE tb(姓名 VARCHAR(10),语文 INT,数学 INT,物理 INT)

INSERT INTO tb VALUES('张三',74,83,93)

INSERT INTO tb VALUES('李四',74,84,94)

go

SELECT * FROM tb
复制代码

姓名       语文        数学        物理

---------- ----------- ----------- -----------

张三       74          83          93

李四        74          84          94

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
复制代码

姓名       课程 分数

---------- ---- -----------

李四       语文 74

李四       数学 84

李四       物理 94

张三       语文 74

张三       数学 83

张三       物理 93

  

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姓名')

go
复制代码

 

3、使用SQL Server 2005静态SQL

--SQL SERVER 2005动态SQL

SELECT  姓名 ,
        课程 ,
        分数
FROM    tb UNPIVOT ( 分数 FOR 课程 IN ( [语文], [数学], [物理] ) ) t

4、使用SQL Server 2005动态SQL

复制代码 
--SQL SERVER 2005动态SQL

DECLARE @sql NVARCHAR(4000)

SELECT  @sql = ISNULL(@sql + ',', '') + QUOTENAME(name)
FROM    syscolumns
WHERE   id = OBJECT_ID('tb')
        AND name NOT IN ( '姓名' )
ORDER BY colid

SET @sql = 'select 姓名,[课程],[分数] from tb unpivot ([分数] for [课程] in(' + @sql
    + '))b'

EXEC(@sql)
复制代码


行转列，并去重合并

表结构：

create table DSE_WATER_CONSUMPTION_TARGET (
   WRCS_CD              varchar(16)          not null, -- 地区ID 
   FILL_YR              char(4)              not null, -- 填报年份
   WATWR_M              numeric(12,3)        null, -- 水资源总量
   TOT_W                numeric(12,3)        null, -- 用水量
   TOT_WC               numeric(12,3)        null, -- 耗水量
   TOT_WC_R             numeric(5,2)         null, -- 耗水率
   TPA_WU               numeric(8,1)         null, -- 人均生活用水量
   IND_AV_WU            numeric(8,1)         null, -- 万元工业增加值用水量
   GDP_WU               numeric(8,1)         null, -- 万元GDP用水量
   WAT_R                numeric(5,2)         null, -- 水资源利用率
   constraint PK_DSE_WATER_CONSUMPTION_TARGE primary key nonclustered (WRCS_CD, FILL_YR)
)

示例

USE [waterhz]
GO
/****** Object:  StoredProcedure [dbo].[WATERHZ_PRO_USER_INDEX_QUERY]    Script Date: 05/22/2015 13:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[WATERHZ_PRO_USER_INDEX_QUERY]
	@START_DATE smallint,
	@END_DATE smallint,
	@type TINYINT -- 1:水资源总量,2:用水量,3:耗水量,4:耗水率,
	-- 5:人居生活用水量,6:万元工业增加值用水量,7:万元GDP用水量,8:水资源利用率
AS
	DECLARE @sql VARCHAR(8000);
	DECLARE @SQLlEN INT;
	DECLARE @TEMP_SQL VARCHAR(500);
	DECLARE @YEAR_STR VARCHAR(8000);
	DECLARE @TEMP_NAME VARCHAR(10);
BEGIN
	SET @sql=''  --初始化变量 @sql
	SELECT @sql= @sql+'","' + FILL_YR FROM DSE_WATER_CONSUMPTION_TARGET WHERE FILL_YR>=@START_DATE AND FILL_YR<=@END_DATE GROUP BY FILL_YR --变量多值赋
	SET @sql= STUFF(@sql,1,2,'') --去掉首个','
	SET @sql= @sql + '"' --去掉首个',' 
	-- SET @SQLlEN = LEN(@sql) 
	SET @SQLlEN = 0
	SET @TEMP_SQL = ''
	SET @YEAR_STR = @sql
	PRINT @YEAR_STR
	WHILE @SQLlEN < LEN(@YEAR_STR)
	BEGIN
		IF @SQLlEN <= 6 BEGIN
			SET @TEMP_NAME = SUBSTRING(@sql,1,6);
			SET @TEMP_SQL = 'SUM('+ @TEMP_NAME +') AS ' + @TEMP_NAME;
			SET @SQLlEN += 8;
		END
		ELSE BEGIN
			SET @TEMP_NAME = SUBSTRING(@sql,@SQLlEN,6);
			SET @TEMP_SQL = @TEMP_SQL + ',SUM('+@TEMP_NAME+') as ' + @TEMP_NAME ;
			SET @SQLlEN += 7;
		END
	END
	SET @sql='select ' +@TEMP_SQL+ ',(select wwc.WRCS_NM from WR_WRCS_M wwc where wwc.WRCS_CD=c.WRCS_CD) name from (
	select '+@sql+',WRCS_CD from DSE_WATER_CONSUMPTION_TARGET pivot (' +
	case @type
	when 1 then 'max(WATWR_M)'
	when 2 then 'max(TOT_W)'
	when 3 then 'max(TOT_WC)'
	when 4 then 'max(TOT_WC_R)'
	when 5 then 'max(TPA_WU)'
	when 6 then 'max(IND_AV_WU)'
	when 7 then 'max(GDP_WU)'
	when 8 then 'max(GDP_WU)'
	else 'max(WATWR_M)'
	end
	+ 'for FILL_YR in ('+@sql+'))a ) c group by c.WRCS_CD'
	 PRINT @sql
	 -- PRINT @TEMP_SQL
	exec(@sql)
	
END

最终形成的sql

select SUM("2009") AS "2009",SUM("2011") as "2011",SUM("2012") as "2012",SUM("2013") as "2013",SUM("2015") as "2015",(select wwc.WRCS_NM from WR_WRCS_M wwc where wwc.WRCS_CD=c.WRCS_CD) name from (
	select "2009","2011","2012","2013","2015",WRCS_CD from DSE_WATER_CONSUMPTION_TARGET pivot (max(WATWR_M)for FILL_YR in ("2009","2011","2012","2013","2015"))a ) c group by c.WRCS_CD

查询结果



http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html




优游湖
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQLServer行转列函数Pivot
zhengzaifeidelushang的博客
09-23 1796
SQLServer行转列函数Pivot是将关系表（source_table）中的列（pivot_column）的值，转换成另一个关系表（pivot_table）的列名。
sqlserver 行转列，列转行
qq_41942413的博客
02-24 1799
这些语句可以帮助将行转列或列转行，并且可以根据具体的业务需求来使用不同的聚合函数和列值。在实际使用中，需要根据具体的数据结构和业务需求来灵活运用这些语句。要在 SQL Server 中实现行转列和列转行，可以使用 PIVOT 和 UNPIVOT 语句。行转列（case when then）
SqlServer行转列&列转行
热门推荐
m0_49844045的博客
01-06 3万+
2、SQLServer 2.1、行转列 -- 第一种方法 select name, max(case cource when '语文' then score else 0 end) as chinese, max(case cource when '数学' then score else 0 end) as math, max(case cource when '英语' then score else 0 end) as english, max(case cource when '物理' then sc
Sqlserver行转列pivot()
miaomiaotiaopi的博客
08-02 1万+
sqlserver行转列
sqlserver行转列和列转行
阿杰
09-24 3954
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb 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) ins
sqlserver行转列转化实例
Miracle
08-02 4842
以下为实现了数据表行转列，并实现动态列： -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ================...
SQLSERVER行转列 列转行
qq_25128693的博客
03-01 5961
场景: 2个表 一个存储逗号分隔得系统编号 现在需要将系统编号转换为实际系统名称 1.行转列 将项目表的ApplySys 拆分为多条数据 SELECT ApplySys , * FROM p_project SELECT * FROM dbo.myApplication –行转列 SELECT ProjGuid,ApplySys,c.ApplicationName FROM ( SELECT A.ProjGuid, B.ApplySys FROM( SELECT p_projectId ProjGuid
SQLServer行转列总结
zhanchulan的博客
04-18 1264
SQL 的行转列是一种数据处理技术，可以将查询结果中的多行数据转换为一行数据，方便存储和处理。
SqlServer行转列
zhaorui的博客
06-13 9904
例子演示 测试数据 /*-----1.行转列的测试数据--------------------------*/ IF OBJECT_ID('tbScore') IS NOT NULL DROP TABLE tbScore GO CREATE TABLE tbScore ( 姓名 VARCHAR(10) , 课程 VARCHAR(10) , ...
SQL Server行转列的方法解析
09-09
在SQL Server中，行转列是一种常见的数据转换需求，它将数据库中的行数据转换为列显示，以便于分析和报表制作。以下是对SQL Server行转列方法的详细解析。 首先，我们可以使用SQL Server的`PIVOT`操作来实现行转列...
SQLServer行转列实现思路记录
09-10
在SQL Server中，行转列是一种常见的数据处理技术，它将多行数据转换为单行数据，通过不同的列展示，通常用于数据报表的生成。本文将深入探讨如何在SQL Server中实现这一操作，主要涉及静态SQL、动态SQL以及CASE ...
sql server动态行转列.sql
07-20
sqlserver 动态行专列 避免了数据列过多的时候大量的使用case when then...... 原数据 ： UserName Subject Score Nick 语文 80 Nick 数学 90 Nick 英语 70 Nick 生物 85 Kent 语文 80 Kent 数学 90 Kent ...
QT5 FTP 客户端 实现多文件下载 上传
08-01
QT5 FTP 客户端 实现多文件下载 上传
MATLAB车牌识别实现车牌定位仿真系统（可运行）
08-01
车牌定位分为以下步骤： 图像预处理：读取图像并对其进行预处理，例如灰度化、降噪和增强等。 车牌区域定位：使用图像处理技术，例如边缘检测、连通区域检测和形态学操作等，找到车牌的大致位置。 车牌区域精确定位：通过进一步的图像处理技术，例如投影、边缘检测和形态学操作等，对车牌区域进行精确定位。 字符分割：将车牌区域中的字符进行分割，形成单独的字符图像。 字符识别：使用字符识别算法，例如模板匹配、神经网络或支持向量机等，对分割的字符进行识别。 本设计非常适合新手学习使用，进阶研究等二次开发。欢迎大家多多下载交流学习研究。
年度销售回款计划表（依据项目回款率制定，月度分解.xlsx
最新发布
08-01
销售统计，调查问卷，库存明细，跟进表，业绩统计表，差异分析 ，产品清单 适用人群：学习不同技术领域的小白或进阶学习者；可作为毕设项目、课程设计、大作业、工程实训或初期项目立项。
步控中文编程软件+视频教学案例+国产PLC控制器+中文编程PLC+中文PLC+步控PLC+中文控制器
08-01
中文表格式编程软件，无需使用复杂的编程语言，低代码编程，快速入门，功能模块化。 零基础：软件是全中文表格式界面，不需要懂英文代码，完全可以做到零基础使用，像工厂里的电工、装配工都可以直接上手。 降成本：软件可以给标准的控制器编写程序，还可以给批量生产的设备定制开发板，大大降低设备成本。 无门槛：软件使用底层程序做好的功能模块，直接调用即可，程序单一性，调试修改方便。
Transformer
08-01
transformer视频
sqlserver 行转列
08-30
在SQL Server中，行转列是一种将表中的行数据转换为列数据的操作。根据提供的引用内容，我们可以使用PIVOT函数来实现行转列操作。首先，创建一个临时表#USER，并插入一些数据。然后使用PIVOT函数将科目列转换为新的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值