SQL学习（一）--数据库行列转换

最新推荐文章于 2022-09-16 10:52:49 发布

原创最新推荐文章于 2022-09-16 10:52:49 发布 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

本文介绍了SQLServer中UNPIVOT和PIVOT操作符的使用方法，包括语法详解及示例。UNPIVOT用于将列转换为行，而PIVOT则用于行转列的操作，帮助用户更灵活地处理数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQLServer

1、UNPIVOT 操作符

UNPIVOT操作符就是取得一个行的数据集合，然后把每一行都转换成多个行数据。

UNPIVOT 语法

下面就是 UNPIVOT 的语法:

SELECT [columns not unpivoted],
	 [unpivot_column],
       [value_column],
FROM
(<source query>)
AS <alias for the source data>
UNPIVOT ( [value_column] FOR [unpivot_column] IN ( <column_list> ) ) 
   AS <alias for unpivot>

Where:

- [columns not unpivoted]: 不被转换的列的名字清单。
- [unpivot_column]: 不转换的列的名称。
- [value_column]: 确定一个列名称来代表不转换的列的数据。
- <source query>: 源数据。
<alias for the source data>: 为源数据转换后的表确定一个别名。
<column_list>: 被转换的列的列名称。
<alias for unpivot>: 为转换操作的整套生产，确定一个别名。

为了更好地理解我们看下面的例子：

简单的例子

USE tempdb;
GO
IF object_id('PhoneNumbers') IS NOT NULL DROP TABLE PhoneNumbers;
GO
CREATE TABLE PhoneNumbers (
	PersonID int, 
	HomePhone varchar(12),
	CellPhone varchar(12), 
	Workphone varchar(12), 
	FaxNumber varchar(12));

INSERT INTO PhoneNumbers VALUES 
	(1,Null,'444-555-2931',Null,Null),
	(2,'444-555-1950','444-555-2931',Null, Null),
	(3,'444-555-1950', Null,'444-555-1324','444-555-2310'),
	(4,'444-555-1950','444-555-2931','444-555-1324',
        '444-555-1987');

SELECT PersonID, PhoneType, PhoneNumber
FROM (
	SELECT PersonID, HomePhone, CellPhone, Workphone, FaxNumber
	FROM PhoneNumbers ) AS Src
	UNPIVOT (
		PhoneNumber FOR PhoneType IN 
		(HomePhone, CellPhone, WorkPhone, FaxNumber)) AS UNPVT;

使用两个UNPIVOT操作符

第二个例子中，我将使用两个操作符来行转列来转换一套名字/值的两列数据。具体如下：

在表 CustPref里面我有四对名称和值。

我们将使用不同的UNPIVOT操作符来创建一个结果集，每一个PrefType的名字和值针对每个CustID 和CustName。并联使用操作符的作用是为了转换两组列。这样讲能表示为一个参数名称和值在一行里面。执行代码如下：

http://www.cnblogs.com/wenBlog/

通过这个输出结果，能发现不同的type对应不同的值得列，并且要关联CustID。整个查询通过两个不同的UNPOVOT操作符同时使用了where 子句来合并输出结果（基于列名前五个字符相同的进行匹配），第一个行转列转换的是数据，第二个为类型，where限制了比较前五个字符，我能取得匹配的数据组。

2、PIVOT 操作符（行转列）

 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

				
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

得到的结果如下：

MySQL

列转行：利用max(case when then)

 
   
        -- ------------------------
       
 
        -- 列转行统计数据
       
 
        -- ------------------------
       
 
        SELECT 
        Date 
        ,
       
 
        MAX
        (
        CASE 
        NAME 
        WHEN 
        '小说' 
        THEN 
        Scount 
        ELSE 
        0 
        END 
        ) 小说,
       
 
        MAX
        (
        CASE 
        NAME 
        WHEN 
        '微信' 
        THEN 
        Scount 
        ELSE 
        0 
        END 
        ) 微信 
       
 
        FROM 
        TabName  
       
 
        GROUP 
        BY 
        Date  
        <br><br>
       
 
 

行转列：利用graup_cancat(name1,”;”,”name2”)

 
        select
       
        Date
        , group_concat(
        NAME
        ,
        '总量:'
        ,Scount) 
        as 
        b_str 
        from   
        TabName 
       
        group 
        by 
        Date

 
   
        select 
        Date
        ,
        NAME
        , group_concat(
        NAME
        ,
        '总量:'
        ,Scount) 
        as 
        b_str 
        from   
        TabName 
       
 
         
        group 
        by 
        Date 
        ,
        NAME
       
 
 