柚子快報激活碼778899分享：sql列轉(zhuǎn)行 行轉(zhuǎn)列

http://yzkb.51969.com/

列轉(zhuǎn)行

在 SQL 中，轉(zhuǎn)換數(shù)據(jù)以按列排列的值成為按行排列的值（即所謂的“列轉(zhuǎn)行”或“列轉(zhuǎn)行”）是常見的數(shù)據(jù)操作需求。這個操作在不同的數(shù)據(jù)庫管理系統(tǒng)中可以通過不同的技術(shù)手段來實現(xiàn)。以下是幾種常見的數(shù)據(jù)庫系統(tǒng)中實現(xiàn)列轉(zhuǎn)行的方法：

1. 使用?UNION ALL（適用于所有 SQL 數(shù)據(jù)庫）

如果你的列數(shù)是固定的，可以使用?UNION ALL?將多個列的數(shù)據(jù)合并到一個新的結(jié)果集的單一列中。

示例數(shù)據(jù)

假設(shè)有一個表?Sales：

product_idsales_q1sales_q2sales_q3sales_q411001502002502110160210260

查詢

以下是一個將每個季度的銷售數(shù)據(jù)轉(zhuǎn)為行的查詢示例：

sql

復制代碼

SELECT product_id, 'Q1' AS quarter, sales_q1 AS sales FROM Sales

UNION ALL

SELECT product_id, 'Q2', sales_q2 FROM Sales

UNION ALL

SELECT product_id, 'Q3', sales_q3 FROM Sales

UNION ALL

SELECT product_id, 'Q4', sales_q4 FROM Sales;

結(jié)果：

product_idquartersales1Q11001Q21501Q32001Q42502Q11102Q21602Q32102Q4260

2. 使用?UNPIVOT（適用于 Oracle 和 SQL Server）

對于支持?UNPIVOT?操作的數(shù)據(jù)庫，如 Oracle 和 SQL Server，可以直接使用?UNPIVOT?函數(shù)來實現(xiàn)。

Oracle 示例

sql

復制代碼

SELECT product_id, quarter, sales

FROM Sales

UNPIVOT (

sales FOR quarter IN (sales_q1 AS 'Q1', sales_q2 AS 'Q2', sales_q3 AS 'Q3', sales_q4 AS 'Q4')

);

SQL Server 示例

sql

復制代碼

SELECT product_id, quarter, sales

FROM Sales

UNPIVOT (

sales FOR quarter IN ([sales_q1], [sales_q2], [sales_q3], [sales_q4])

) AS Unpvt;

3. 使用?CROSS APPLY（適用于 SQL Server）

在 SQL Server 中，CROSS APPLY?也可以用于列轉(zhuǎn)行操作。

示例數(shù)據(jù)

假設(shè)有一個表?Sales：

sql

復制代碼

CREATE TABLE Sales (

product_id INT,

sales_q1 INT,

sales_q2 INT,

sales_q3 INT,

sales_q4 INT

);

INSERT INTO Sales (product_id, sales_q1, sales_q2, sales_q3, sales_q4) VALUES

(1, 100, 150, 200, 250),

(2, 110, 160, 210, 260);

查詢

sql

復制代碼

SELECT

product_id,

quarter,

sales

FROM

Sales

CROSS APPLY

(VALUES

('Q1', sales_q1),

('Q2', sales_q2),

('Q3', sales_q3),

('Q4', sales_q4)

) AS Quarters (quarter, sales);

結(jié)果：

product_idquartersales1Q11001Q21501Q32001Q42502Q11102Q21602Q32102Q4260

4. 使用?LATERAL VIEW（適用于 Hive）

在 Hive 中，LATERAL VIEW?和?explode?函數(shù)可以實現(xiàn)類似的操作。

示例數(shù)據(jù)

假設(shè)有一個表?Sales：

plaintext

復制代碼

product_id | sales_q1 | sales_q2 | sales_q3 | sales_q4

1 | 100 | 150 | 200 | 250

2 | 110 | 160 | 210 | 260

查詢

sql

復制代碼

SELECT product_id, quarter, sales

FROM Sales

LATERAL VIEW explode(array(

named_struct('quarter', 'Q1', 'sales', sales_q1),

named_struct('quarter', 'Q2', 'sales', sales_q2),

named_struct('quarter', 'Q3', 'sales', sales_q3),

named_struct('quarter', 'Q4', 'sales', sales_q4)

)) T AS quarter_sales

結(jié)果：

product_idquartersales1Q11001Q21501Q32001Q42502Q11102Q21602Q32102Q4260

結(jié)論

UNION ALL：適用于所有 SQL 數(shù)據(jù)庫，適用于小規(guī)模、列數(shù)固定的情況。UNPIVOT：適用于 Oracle 和 SQL Server，適用于需要靈活處理列轉(zhuǎn)行數(shù)據(jù)的情況。CROSS APPLY：適用于 SQL Server，提供了更靈活的列轉(zhuǎn)行操作。LATERAL VIEW：適用于 Hive，處理大規(guī)模數(shù)據(jù)集合的列轉(zhuǎn)行。

在 SQL 中，行轉(zhuǎn)列（也稱為“透視”或“旋轉(zhuǎn)”）的操作是將行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)，這在數(shù)據(jù)總結(jié)和分析中非常有用。不同的數(shù)據(jù)庫系統(tǒng)有不同的方法來實現(xiàn)行轉(zhuǎn)列的操作。以下是一些常見數(shù)據(jù)庫系統(tǒng)中的行轉(zhuǎn)列技術(shù)和示例。

行轉(zhuǎn)列

1. 使用?CASE?和?GROUP BY（適用于所有 SQL 數(shù)據(jù)庫）

這種方法適用于所有支持 SQL 的數(shù)據(jù)庫，適用于數(shù)據(jù)量不大的簡單場景。

示例數(shù)據(jù)

假設(shè)有一個表?Sales：

product_idquartersales1Q11001Q21501Q32001Q42502Q11102Q21602Q32102Q4260

查詢

使用?CASE?語句和?GROUP BY?來實現(xiàn)行轉(zhuǎn)列：

sql

復制代碼

SELECT

product_id,

SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS sales_q1,

SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS sales_q2,

SUM(CASE WHEN quarter = 'Q3' THEN sales ELSE 0 END) AS sales_q3,

SUM(CASE WHEN quarter = 'Q4' THEN sales ELSE 0 END) AS sales_q4

FROM Sales

GROUP BY product_id;

結(jié)果：

product_idsales_q1sales_q2sales_q3sales_q411001502002502110160210260

2. 使用?PIVOT（適用于 Oracle 和 SQL Server）

對于支持?PIVOT?操作的數(shù)據(jù)庫，如 Oracle 和 SQL Server，可以直接使用?PIVOT?函數(shù)來實現(xiàn)。

Oracle 示例

sql

復制代碼

SELECT *

FROM (

SELECT product_id, quarter, sales

FROM Sales

)

PIVOT (

SUM(sales) FOR quarter IN ('Q1' AS sales_q1, 'Q2' AS sales_q2, 'Q3' AS sales_q3, 'Q4' AS sales_q4)

);

SQL Server 示例

sql

復制代碼

SELECT product_id, sales_q1, sales_q2, sales_q3, sales_q4

FROM (

SELECT product_id, quarter, sales

FROM Sales

) AS SourceTable

PIVOT (

SUM(sales) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])

) AS PivotTable;

結(jié)果與上一個方法相同：

product_idsales_q1sales_q2sales_q3sales_q411001502002502110160210260

3. 使用動態(tài) SQL（適用于列名不固定的情況）

當需要處理列名不固定的情況時，可以使用動態(tài) SQL。這里以 SQL Server 為例：

動態(tài) SQL 示例

sql

復制代碼

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 獲取所有的quarter值，并用逗號拼接起來

SELECT @columns = STRING_AGG(QUOTENAME(quarter), ', ')

FROM (SELECT DISTINCT quarter FROM Sales) AS Quarters;

-- 創(chuàng)建動態(tài)SQL語句

SET @sql = '

SELECT product_id, ' + @columns + '

FROM

(

SELECT product_id, quarter, sales

FROM Sales

) AS SourceTable

PIVOT

(

SUM(sales) FOR quarter IN (' + @columns + ')

) AS PivotTable';

-- 執(zhí)行動態(tài)SQL

EXEC sp_executesql @sql;

這種方法可以處理列名不固定的情況，動態(tài)生成列名列表，并執(zhí)行?PIVOT?操作。

4. 在 PostgreSQL 中使用?crosstab

在 PostgreSQL 中，可以使用?crosstab?函數(shù)來進行行轉(zhuǎn)列操作。首先需要安裝?tablefunc?擴展：

sql

復制代碼

CREATE EXTENSION IF NOT EXISTS tablefunc;

然后，可以使用?crosstab?函數(shù)：

示例數(shù)據(jù)

sql

復制代碼

CREATE TABLE Sales (

product_id INT,

quarter TEXT,

sales INT

);

INSERT INTO Sales (product_id, quarter, sales) VALUES

(1, 'Q1', 100),

(1, 'Q2', 150),

(1, 'Q3', 200),

(1, 'Q4', 250),

(2, 'Q1', 110),

(2, 'Q2', 160),

(2, 'Q3', 210),

(2, 'Q4', 260);

查詢

sql

復制代碼

SELECT *

FROM crosstab(

'SELECT product_id, quarter, sales FROM Sales ORDER BY product_id, quarter',

'SELECT DISTINCT quarter FROM Sales ORDER BY quarter'

) AS ct (product_id INT, sales_q1 INT, sales_q2 INT, sales_q3 INT, sales_q4 INT);

結(jié)果：

product_idsales_q1sales_q2sales_q3sales_q411001502002502110160210260

結(jié)論

CASE?和?GROUP BY：適用于所有 SQL 數(shù)據(jù)庫，適合數(shù)據(jù)量不大的簡單場景。PIVOT：適用于 Oracle 和 SQL Server，適合需要靈活處理列轉(zhuǎn)行數(shù)據(jù)的情況。動態(tài) SQL：適用于列名不固定的情況，通過動態(tài)生成 SQL 語句實現(xiàn)。crosstab：適用于 PostgreSQL，通過?tablefunc?擴展實現(xiàn)行轉(zhuǎn)列操作。

柚子快報激活碼778899分享：sql列轉(zhuǎn)行 行轉(zhuǎn)列

http://yzkb.51969.com/

精彩文章