`

SQL中PIVOT 行列转换

 
阅读更多

PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT与PIVOT执行相反的操作,将表值表达式的列转换为列值。


通俗简单的说:PIVOT就是行转列,UNPIVOT就是列传行

 

一、PIVOT实例

1. 建表

建立一个销售情况表,其中,year字段表示年份,quarter字段表示季度,amount字段表示销售额。quarter字段分别用Q1, Q2, Q3, Q4表示一、二、三、四季度。

 
 CREATE TABLE SalesByQuarter
    (    year INT,    -- 年份
        quarter CHAR(2),  -- 季度
        amount MONEY  -- 总额
    )
 

2. 填入表数据

使用如下程序填入表数据。

 
SET NOCOUNT ON
    DECLARE @index INT
    DECLARE @q INT
    SET @index = 0
    DECLARE @year INT
    while (@index < 30)
    BEGIN
        SET @year = 2005 + (@index % 4)
        SET @q = (CAST((RAND() * 500) AS INT) % 4) + 1
        INSERT INTO SalesByQuarter VALUES (@year, 'Q' + CAST(@q AS CHAR(1)), RAND() * 10000.00)
        SET @index = @index + 1
    END
 

3、如果我们要比较每年中各季度的销售状况,要怎么办呢?有以下两种方法:

 

(1)、使用传统Select的CASE语句查询

在SQL Server以前的版本里,将行级数据转换为列级数据就要用到一系列CASE语句和聚合查询。虽然这种方式让开发人员具有了对所返回数据进行高度控制的能力,但是编写出这些查询是一件很麻烦的事情。

 
    SELECT year as 年份
        , sum (case when quarter = 'Q1' then amount else 0 end) 一季度
        , sum (case when quarter = 'Q2' then amount else 0 end) 二季度
        , sum (case when quarter = 'Q3' then amount else 0 end) 三季度
        , sum (case when quarter = 'Q4' then amount else 0 end) 四季度
    FROM SalesByQuarter GROUP BY year ORDER BY year DESC
 

得到的结果如下:
 


 

(2)、使用PIVOT


由于SQL Server 2005有了新的PIVOT运算符,就不再需要CASE语句和GROUP BY语句了。(每个PIVOT查询都涉及某种类型的聚合,因此你可以忽略GROUP BY语句。)PIVOT运算符让我们能够利用CASE语句查询实现相同的功能,但是你可以用更少的代码就实现,而且看起来更漂亮。
 

 
SELECT year as 年份, Q1 as 一季度, Q2 as 二季度, Q3 as 三季度, Q4 as 四季度 FROM SalesByQuarter PIVOT (SUM (amount) FOR quarter IN (Q1, Q2, Q3, Q4) ) AS P ORDER BY YEAR DESC
 

得到的结果如下:
 

 

 

二、通过下面一个实例详细介绍PIVOT的过程

 

 
SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步(选择行转列后的结果集的列)这里可以用“*”表示选择所有列,也可以只选择某些列(也就是某些天)
FROM WEEK_INCOME --这里是PIVOT第二步骤(准备原始的查询结果,因为PIVOT是对一个原始的查询结果集进行转换操作,所以先查询一个结果集出来)这里可以是一个select子查询,但为子查询时候要指定别名,否则语法错误
PIVOT
(
    SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])--这里是PIVOT第一步骤,也是核心的地方,进行行转列操作。聚合函数SUM表示你需要怎样处理转换后的列的值,是总和(sum),还是平均(avg)还是min,max等等。例如如果week_income表中有两条数据并且其week都是“星期一”,其中一条的income是1000,另一条income是500,那么在这里使用sum,行转列后“星期一”这个列的值当然是1500了。后面的for [week] in([星期一],[星期二])中 for [week]就是说将week列的值分别转换成一个个列,也就是“以值变列”。但是需要转换成列的值有可能有很多,我们只想取其中几个值转换成列,那么怎样取呢?就是在in里面了,比如我此刻只想看工作日的收入,在in里面就只写“星期一”至“星期五”(注意,in里面是原来week列的值,"以值变列")。总的来说,SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来,就是说:将列[week]值为"星期一","星期二","星期三","星期四","星期五","星期六","星期日"分别转换成列,这些列的值取income的总和。
)TBL--别名一定要写
 
三.UNPIVOT
 
 
很明显,UN这个前缀表明了,它做的操作是跟PIVOT相反的,即列转行。UNPIVOT操作涉及到以下三个逻辑处理阶段。
 
1,生成副本
2,提取元素
3,删除带有NULL的行
 
 
UNPIVOT实例
 
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
 

上面UNPIVOT实例的分析

 

UNPIVOT的输入是左表表达式P,第一步,先为P中的行生成多个副本,在UNPIVOT中出现的每一列,都会生成一个副本。因为这里的IN子句有5个列名称,所以要为每个来源行生成5个副本。结果得到的虚拟表中将新增一个列,用来以字符串格式保存来源列的名称(for和IN之间的,上面例子是 Employee )。第二步,根据新增的那一列中的值从来源列中提取出与列名对应的行。第三步,删除掉结果列值为null的行,完成这个查询。

分享到:
评论

相关推荐

    深入SQL中PIVOT 行列转换详解

    在SQL中,PIVOT运算符是一个强大的工具,它允许数据分析师和数据库管理员将数据的行转换为列,从而提供了一种更直观的方式来查看和分析数据。这种转换在数据分析和报表制作中尤其有用,因为它可以帮助将大量行数据...

    SQL 2008行列转换的pivot

    ### SQL 2008 行列转换 (Pivot) 的动态实现 #### 知识点一:行列转换(Pivot)的概念与应用场景 在数据库查询中,有时我们需要将数据表中的行转换为列,或者将列转换为行,这种操作被称为行列转换。行列转换在报表...

    SQL行列转换 Pivot UnPivot

    Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要作用是将列值旋转为列名,即行转列。其基本语法为: ``` SELECT * FROM table_source PIVOT (聚合函数(value_...

    sql server 行列转换

    通过上述示例可以看出,使用动态SQL可以有效地实现SQL Server中的行列转换。虽然这种方法具有一定的灵活性和适用性,但在实际应用中还需要考虑其潜在的安全性和维护性问题。在实际开发过程中,应根据具体需求和场景...

    sql查询中行列转换

    ### SQL查询中的行列转换 在数据库管理中,经常会遇到数据结构需要调整的情况,尤其是当原始数据的排列方式与实际需求不一致时。本篇文章将详细解释如何通过一条SQL查询语句实现行列转换,并且会针对两种不同的SQL ...

    通过SQL语句实现行列转换的几种方法

    ### 通过SQL语句实现行列转换的几种方法 在日常工作中,我们经常需要处理的数据结构并不总是按照我们期望的方式组织的。特别是在制作自定义报表或进行产品开发时,经常会遇到需要将数据从一种布局转换到另一种布局...

    SQL语句行列转换(附带数据库、表、视图操作)

    SQL 语句行列转换是数据库管理系统中的一种常见操作,它可以将数据从行转换为列,或者从列转换为行。在这个过程中,需要使用数据库管理语言(Database Management Language,DML)来实现数据的转换。下面我们将详细...

    oracle sql 行列转换

    在Oracle SQL中,行列转换是一种常见的数据操作需求,主要用于将数据表中的行转换为列,或将列转换为行,以此来满足不同的数据展示或分析需求。这种转换在处理多维度数据、汇总数据或是进行复杂查询时特别有用。下面...

    sql动态转换行列和字符的分割

    在SQL中,行列转换通常通过`PIVOT`和`UNPIVOT`操作实现。`PIVOT`用于将行数据转换为列数据,而`UNPIVOT`则相反。例如,如果你有一个销售数据表,其中包含产品、月份和销售额,你可以使用`PIVOT`将每个月的销售额...

    sql试题及答案,sql 行列转换,sql存储过程实例

    4. **CROSS APPLY或OUTER APPLY**:在某些SQL版本中,可以使用这些运算符配合子查询进行行列转换。 三、SQL 存储过程实例 存储过程是预编译的SQL语句集合,可以提高执行效率,简化复杂的操作,并提供更好的安全性。...

    SQL 数据行列转换

    2. **FOR XML PATH() 函数的应用**:详细解释FOR XML PATH()函数在行列转换中的作用及其实现方法。 3. **动态SQL的使用**:探讨如何通过动态SQL来解决行列转换过程中遇到的问题。 4. **实际案例分析**:通过一个具体...

    sql server通过pivot对数据进行行列转换的方法

    说明:sql server如何通过pivot对数据进行行列转换 */ create table #t ( [员工工号] nvarchar(50), [员工姓名] nvarchar(50), [月份] nvarchar(50), [工资] int ) insert into #t values(N'01', N'张三', N'...

    SQL行列转换

    在实际的数据分析和报表制作过程中,我们经常需要将数据的列与行进行互换,这就是所谓的“SQL行列转换”。这种操作在处理汇总数据、创建透视表或者进行特定统计分析时尤其常见。以下我们将详细探讨SQL中的行列转换...

    SQL语句普通行列转换.docx

    本文档详细介绍了如何使用SQL Server中的`PIVOT`和`UNPIVOT`函数进行数据的行列转换,包括静态和动态SQL的实现方法。这些技术可以帮助我们在处理复杂的数据展示需求时更加灵活高效。在实际应用中,应根据具体情况...

    Oracle中SQL语句行列之间相互转换

    通过以上介绍,我们可以看到在Oracle数据库中实现行列转换并不复杂,主要通过`UNPIVOT`和`PIVOT`操作来完成。这些操作不仅能够帮助我们更灵活地处理数据,还能够提高数据的可读性和分析效率。在实际应用中,应根据...

    SQL2000 和 SQL2005 下 行列转换 示例 - freeliver54 - 博客园.pdf

    在SQL Server 2005中,可以通过PIVOT函数更加方便地实现行转列操作: ```sql SELECT StuName, [语文] AS '语文', [数学] AS '数学', [历史] AS '历史' FROM StuSources PIVOT ( SUM(ChengJi) FOR KeCheng IN ...

    SQL Server 2005新功能之PIVOT的描述

    在SQL Server 2005中,PIVOT是一个重要的新特性,它使得数据的行列转换更为便捷。在SQL Server 2000及其以前版本,如果需要将数据的某列值转换为行对应的列,通常需要使用复杂的CASE语句配合聚合函数(如SUM)来实现...

Global site tag (gtag.js) - Google Analytics