SQL行转列汇总

最新推荐文章于 2024-04-22 18:52:42 发布
最新推荐文章于 2024-04-22 18:52:42 发布
阅读量358 收藏
点赞数
分类专栏： 行转列 列转行 SQL行转列 SQL列转行 SQL 行转列 SQL 列转行 文章标签： 行转列 列转行 SQL行转列 SQL列转行 SQL 行转列

SQL行转列汇总

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

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

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

SQL2008 中可以直接使用

完整语法：

 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)
复制代码
cdjj_灰少
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
table 行转列的sql详解
09-11
SQL中的“行转列”是一种常见的数据操作，用于将表格中某一列的数据转化为多列显示，通常涉及数据透视或聚合函数。在这个实例中，我们将探讨如何实现这个过程，以帮助初学者理解其背后的逻辑。 首先，我们创建一个...
將行转成列的统计SQL
Compile failed
03-31 1542
create table #t(shopid varchar(10), procid varchar(10), qty int)insert into #tselect AAAAA,    00001,   10union all select AAAAA,    00002,   20union all select AAAAA,    00003,   10union
建议收藏丨sql行转列的一千种写法！！
2301_82244623的博客
04-22 556
-【
isnull pivot server sql_SQL Server中行列转换 Pivot UnPivot
weixin_39934085的博客
12-20 171
PIVOT用于将列值旋转为列名(即行转列)，在SQL Server2000可以用聚合函数配合CASE语句实现PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P完整语法：table_sourcePIVOT(聚合函数(value_column)FOR pivot_columnIN())UNPIVOT用于将列明转为列值(即列转行)，在SQL Server 2000可...
用PIVOT与UNPIVOT函数 行转列，列转行
apple_任
09-02 318
PIVOT: 将维度数据拆解成多个列 --对应 SUM(CASE WHEN …) UNPIVOT：将多个列合并成一个新的维度 --对应 UNION ALL SELECT * FROM SCORE_1; SELECT * FROM SCORE_2; SELECT STUDENT, SUM(CASE WHEN COURSE = ‘CHINESE’ THEN SCORE END) CHINESE, SUM(CASE WHEN COURSE = ‘MATH’ THEN SCORE END) MATH, SUM(
SQL基础教程之行转列Pivot函数
09-09
例如，在Oracle中，你可以这样实现行转列： ```sql SELECT * FROM DailyIncome PIVOT ( SUM(IncomeAmount) FOR IncomeDay IN ('FRI', 'MON', 'SUN', 'THU', 'TUE', 'WED', 'SAT') ); ``` 这里，Oracle的`PIVOT`...
SQL行转列、列转行的简单实现
09-09
在SQL数据库操作中，行转列和列转行是数据处理和分析中常见的需求，尤其在报表制作和数据展示时。这两种转换可以帮助我们更有效地组织和呈现数据。本篇文章将详细探讨MySQL中如何实现行转列和列转行的操作。 **行转...
selectcase实现行转列.txt
11-03
本sql实现了数据库行转列的汇总查询，一条sql搞定全部计算
SQL知识点之列转行Unpivot函数
09-09
Pivot函数是用于将数据的行转换为列，通常用于数据透视或汇总，使得特定的值作为列头，而其他列的值作为行的数据。与Pivot相反，Unpivot则是将列的数据转化为行，这样可以更容易地对单一属性的不同值进行分析。 在...
简单的行转列，成绩统计方式，oracle和mysql通用方式
豆芽饭团的博客
05-17 647
在看行转列的时候，看到网上很多资料都写的太复杂，我记录一个比较简单的方式，作为自己行转列的初步学习可以看看，解决比较复杂的业务问题可能帮不上忙，勿喷。 主要实现思路就是，先将行转成列，但是这个时候，会出现： 执行语句： SELECT NAME 姓名, CASE SUBJECT WHEN '语文' THEN score END 语文, CASE SU...
org.springframework.jdbc.UncategorizedSQLExceptionexpect：RPAREN, actual LPAREN unpivot
gzc_870301的博客
06-08 2178
org.springframework.jdbc.UncategorizedSQLException：sql injection violation  RPAREN, actual LPAREN unpivotsql语句注入失败，使用的是oracle 数据库，在plsql里面能执行，放到项目中报错。select distinct s.id,s.userName,s.realname from Tw...
MySQL 列转行统计查询 、行转列统计查询
weixin_44138647的博客
01-02 434
首先我们创建表： -- ---------------------------- -- Table structure for tabname -- ---------------------------- DROP TABLE IF EXISTS `tabname`; CREATE TABLE `tabname` ( `Id` int(11) NOT NULL AUTO_INCREME...
SQL行列转换-sum函数的妙用
二八定律法则
05-18 895
sum函数不止可以求和，还可以行列转换
SQL行列转换
热门推荐
Hudas的博客
04-09 2万+
本文主要介绍SQL常见的行列转换
每日学习心得：SQL查询表的行列转换/小计/统计（with rollup，with cube，pivot解析）...
weixin_33898876的博客
08-23 276
原文 http://www.cnblogs.com/Olive116/p/3271706.html 2013-8-20 1.    SQL查询表的行列转换/小计/统计（with  rollup，with cube，pivot解析） 在实际的项目开发中有很多项目都会有报表模块，今天就通过一个小的SQL查询统计来讲解一下实际开发中比较常用的行列转换/小计/统计等报表统计相关的常用知识点。 ...
sql 多表查询行转列
最新发布
06-08
SQL中的行转列（也称为“pivot”或“交叉表”操作）是一种数据转换技术，用于将一行数据中的某个字段值作为新列名，将该字段对应的值放在对应的列中。这种操作在汇总或分析多维数据时非常有用，特别是当你需要按照不同的分类对数据进行分组和计数时。 在标准SQL中，没有内置的行转列函数或命令，但可以通过联合（JOIN）、子查询、CASE语句或者使用特定数据库系统的扩展功能（如MySQL的PIVOT或T-SQL的UNPIVOT）来实现。这里是一个简单的例子： 假设你有一个销售数据表（sales），其中包含产品（product）和销售额（amount）两个字段，你想按产品种类（product_type）将总销售额转为各个类型单独的列： 原始表（销售数据表）： | product | amount | |---------|--------| | A | 100 | | B | 200 | | A | 150 | | C | 300 | | B | 250 | 行转列后的结果可能如下（假设产品A的类型是'Electronics'，B的类型是'Clothing')： | product_type | Electronics | Clothing | |-------------|-------------|----------| | Sales | 250 | 450 | 在实际操作中，你可以使用以下SQL查询（假设MySQL的PIVOT语法）： ```sql SELECT * FROM ( SELECT product, product_type, amount FROM sales ) AS source PIVOT ( SUM(amount) FOR product_type IN ('Electronics', 'Clothing') ) AS pivot_table; ``` 如果你使用的是不支持PIVOT的SQL版本，你可能需要编写更复杂的子查询或临时表来完成相同的效果。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值