oracle的行转列函数

格式：docx
大小：37.26 KB
文档页数：5

oracle的行转列函数

Oracle的行转列函数是指将一些列的多个行值转换为一行，通常用于将多行数据合并成单行数据，以便于进行数据汇总或者分析。

在Oracle中，行转列的主要方法有使用PIVOT和UNPIVOT函数以及使用CASE语句进行条件判断。

1.使用PIVOT函数

PIVOT函数用于将行数据转换为列数据。它的语法如下：

PIVOT

聚合函数

FOR列名

IN('列值1'AS'新列名1','列值2'AS'新列名2',...)

;

例如，假设有一个表student，包含了姓名(name)、课程(course)和分数(score)三个字段，如下所示：

name course score

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

Alice Math 85

Alice Chinese 90

Alice English 95 Bob Math 80

Bob Chinese 75

Bob English 85

现在我们希望将每个学生的课程及其对应的分数转换为一行数据，可以使用如下的PIVOT语句：

SELECT*

FROM

SELECT name, course, score

FROM student

PIVOT

AVG(score) -- 可以是其他聚合函数，如SUM、MAX等

FOR course

IN ('Math' AS Math, 'Chinese' AS Chinese, 'English' AS

English)

;

执行该查询后，将得到如下的结果：

name Math Chinese English

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

Alice 85 90 95 Bob 80 75 85

2.使用UNPIVOT函数

UNPIVOT函数是PIVOT函数的逆操作，用于将列数据转换为行数据。它的语法如下：

UNPIVOT

转换列名

FOR列名

IN(新列名1AS'列值1',新列名2AS'列值2',...)

;

例如，假设有一个表student，包含了姓名(name)、Math、Chinese和English三个科目的分数，我们希望将这三个科目的分数转换为一列数据，可以使用如下的UNPIVOT语句：

SELECT*

FROM

SELECT name, Math, Chinese, English

FROM student

UNPIVOT

score

FOR course IN (Math AS 'Math', Chinese AS 'Chinese', English AS

'English')

;

执行该查询后，将得到如下的结果：

name course score

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

Alice Math 85

Alice Chinese 90

Alice English 95

Bob Math 80

Bob Chinese 75

Bob English 85

3.使用CASE语句进行条件判断

除了使用PIVOT和UNPIVOT函数外，我们还可以使用CASE语句进行条件判断，实现行转列的功能。

例如，假设有一个表student，包含了姓名(name)、课程(course)和分数(score)三个字段，我们希望将每个学生的课程及其对应的分数转换为一行数据，可以使用如下的CASE语句：

SELECT name,

MAX(CASE WHEN course = 'Math' THEN score END) AS Math, MAX(CASE WHEN course = 'Chinese' THEN score END) AS Chinese,

MAX(CASE WHEN course = 'English' THEN score END) AS English

FROM student

GROUP BY name ;

执行该查询后，将得到如下的结果：

name Math Chinese English

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

Alice 85 90 95

Bob 80 75 85

总结：

Oracle的行转列函数包括PIVOT和UNPIVOT函数以及使用CASE语句进行条件判断。PIVOT函数用于将行数据转换为列数据，而UNPIVOT函数用于将列数据转换为行数据。另外，还可以使用CASE语句进行条件判断来实现行转列的功能。这些函数和语句提供了灵活的方法来处理行列转换的需求，可以方便地进行数据汇总或者分析。

[MSSQL]採用pivot函数实现动态行转列

[MSSQL]採⽤pivot函数实现动态⾏转列

环境要求：2005+

在⽇常需求中常常会有⾏转列的事情需求处理。假设不是动态的⾏，那么我们能够採取case when 罗列处理。

在sql 2005曾经处理动态⾏或列的时候，通常採⽤拼接字符串的⽅法处理。在2005以后新增了pivot函数之后，我能够利⽤这样函数来处理。

1.动态SQL注⼊式推断函数

--既然是⽤到了动态SQL,就有⼀个⽼话题:SQL注⼊。

建⼀个注⼊性字符的推断函数。CREATE FUNCTION [dbo].[fn_CheckSQLInjection]

(

@Col nvarchar(4000)

)

RETURNS BIT --假设存在可能的注⼊字符返回true,反之返回false

BEGIN

DECLARE @result bit;

UPPER(@Col) LIKE UPPER(N'%0x%')

OR UPPER(@Col) LIKE UPPER(N'%;%')

OR UPPER(@Col) LIKE UPPER(N'%''%')

OR UPPER(@Col) LIKE UPPER(N'%--%')

OR UPPER(@Col) LIKE UPPER(N'%/*%*/%')

OR UPPER(@Col) LIKE UPPER(N'%EXEC%')

OR UPPER(@Col) LIKE UPPER(N'%xp_%')

OR UPPER(@Col) LIKE UPPER(N'%sp_%')

OR UPPER(@Col) LIKE UPPER(N'%SELECT%')

OR UPPER(@Col) LIKE UPPER(N'%INSERT%')

OR UPPER(@Col) LIKE UPPER(N'%UPDATE%')

OR UPPER(@Col) LIKE UPPER(N'%DELETE%')

OR UPPER(@Col) LIKE UPPER(N'%TRUNCATE%')

Oracle行转列（使用pivot函数）

Oracle⾏转列（使⽤pivot函数）

在⽇常使⽤中，经常遇到这样的情况，需要将数据库中⾏转化成列显⽰，如

转化为

这个时候，我们就需要使⽤pivot函数

百度后，参考⽹址，完成了以下操作

with temp as(

select '四川省' nation ,'成都市' city,'第⼀' ranking from dual union all

select '四川省' nation ,'绵阳市' city,'第⼆' ranking from dual union all

select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all

select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all

select '湖北省' nation ,'武汉市' city,'第⼀' ranking from dual union all

select '湖北省' nation ,'宜昌市' city,'第⼆' ranking from dual union all

select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual

)

select * from （select nation,city,ranking from temp）pivot (max(city) for ranking in ('第⼀' as 第⼀,'第⼆' AS 第⼆,'第三' AS 第三,'第四' AS 第四));

这样就顺利的实现了操作，其中关键函数pivot，其⽤法如下

pivot（聚合函数 for 列名 in（类型））

--其中 in(‘’) 中可以指定别名，in中还可以指定⼦查询，⽐如 select distinct ranking from temp

当然也可以不使⽤pivot函数，使⽤下⾯的语句同样可以实现效果

oracle聚合函数LISTAGG，将多行结果合并成一行

oracle聚合函数LISTAGG，将多⾏结果合并成⼀⾏

LISTAGG(列名,' 分割符号')

oracle 11g 以上的版本才有的⼀个将指定列名的多⾏查询结果，⽤指定的分割符号合并成⼀⾏显⽰：

例如：表原始数据：

需求：将 mb1_Transport_License_list 表中的数据，根据 transportation_license_id 数据进⾏分组，对 Item_Category_Name 列的数据进⾏去重合并

使⽤聚合函数 LISTAGG 解决

[sql]

1. SELECT transportation_license_id, 2. LISTAGG( to_char(Item_Category_Name), ',') WITHIN GROUP(ORDER BY Item_Category_Name) AS employees 3. FROM ( select distinct transportation_license_id, item_category_name from mb1_Transport_License_list ) group by transportation_license_id

SQL解析：

select distinct transportation_license_id, item_category_name from mb1_Transport_Lincense_list ; -- 对需要做合并处理的数据源数据进⾏去重处理，如果实际要求不需要去重处理，这⾥可以直接改为表名，（例如： from mb1_Transport_Lincense_list）进⾏查询

LISTAGG( to_char(Item_Category_Name), ',') WITHIN GROUP(ORDER BY Item_Category_Name) -- 将 Item_Category_Name 列的内容以", "进⾏分割合并、排序；to_char(Item_Category_Name) -- to_char(列名) 解决使⽤聚合函数 LISTAGG 进⾏查询后，对查询结果乱码问题进⾏转码处理；

SqlServerPIVOT函数快速实现行转列，UNPIVOT实现列转行（转）

SqlServerPIVOT函数快速实现⾏转列，UNPIVOT实现列转⾏

（转）

我们在写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]

ALTER TABLE [dbo].[ShoppingCart] ADD DEFAULT ((0)) FOR [TotalPrice]

INSERT [dbo].[ShoppingCart] ([Week], [TotalPrice], [GroupId]) VALUES (1, CAST(10 AS Decimal(18, 0)), 1)

下载提示

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

oracle的行转列函数

合集下载

[MSSQL]採用pivot函数实现动态行转列

Oracle行转列（使用pivot函数）

oracle聚合函数LISTAGG，将多行结果合并成一行

SqlServerPIVOT函数快速实现行转列，UNPIVOT实现列转行（转）

文档推荐

最新文档