要將寬表數(shù)據(jù)轉(zhuǎn)換為長(zhǎng)格式，可使用SQL中的UNION ALL、UNPIVOT或LATERAL JOIN方法。 1. 對(duì)於不支持UNPIVOT的數(shù)據(jù)庫(kù)如MySQL，可用UNION ALL逐列合併；2. Oracle或SQL Server支持UNPIVOT語(yǔ)法，能更簡(jiǎn)潔地實(shí)現(xiàn)轉(zhuǎn)換；3. PostgreSQL或MySQL 8 可用LATERAL JOIN結(jié)合VALUES構(gòu)造實(shí)現(xiàn)高效擴(kuò)展的unpivot操作。不同方法各有適用場(chǎng)景，需根據(jù)數(shù)據(jù)庫(kù)類(lèi)型和列數(shù)選擇最優(yōu)方案。

Sometimes, data comes in a wide format where each column represents a different time point or category. To analyze this kind of data more effectively—like plotting trends over time or feeding it into machine learning models—you might need to unpivot it into a long format.

Here's how you can do that directly in SQL.

Use UNION ALL for Simple Unpivoting

If your database doesn't support the UNPIVOT operator (like MySQL), UNION ALL is a solid alternative.

Let's say you have a table like this:

You want to transform it into something like:

The query would look like this:

 SELECT id, name, 'q1' AS quarter, q1_sales AS sales FROM sales_data
UNION ALL
SELECT id, name, 'q2', q2_sales FROM sales_data
UNION ALL
SELECT id, name, 'q3', q3_sales FROM sales_data
UNION ALL
SELECT id, name, 'q4', q4_sales FROM sales_data;

This works well when you have a small number of columns to unpivot. But if you have many columns, writing all these queries manually gets tedious.

Use UNPIVOT in Databases That Support It (eg, Oracle, SQL Server)

Some databases like Oracle and SQL Server have a built-in UNPIVOT clause. This makes the transformation cleaner and easier.

Using the same example as above, here's how you'd write it:

 SELECT id, name, quarter, sales
FROM sales_data
UNPIVOT (
    sales FOR quarter IN (q1_sales AS 'q1', q2_sales AS 'q2', q3_sales AS 'q3', q4_sales AS 'q4')
);

• The UNPIVOT clause takes a value ( sales ) and maps it from multiple columns ( q1_sales , q2_sales , etc.)
• You define what new name each column should get ( AS 'q1' , etc.)

This method is much more compact than using UNION ALL , especially with many columns.

Consider Using Lateral Joins in PostgreSQL or MySQL 8

Another flexible approach, especially useful when dealing with complex transformations, is using lateral joins or derived tables with CROSS JOIN UNNEST() -style syntax.

In PostgreSQL , you could use LATERAL with VALUES :

 SELECT t.id, t.name, s.quarter, s.sales
FROM sales_data t
CROSS JOIN LATERAL (
    VALUES 
        ('q1', t.q1_sales),
        ('q2', t.q2_sales),
        ('q3', t.q3_sales),
        ('q4', t.q4_sales)
) AS s(quarter, sales);

This method is powerful because:

• It's readable
• It scales better than UNION ALL
• It works even when some values are NULL

MySQL 8.0 users can also achieve similar results using common table expressions (CTEs) and UNION ALL , but it's not quite as elegant.

Final Notes

• If you're working with hundreds of columns, consider automating the query generation via scripts.
• Always check which unpivoting methods your specific database supports.
• When performance matters, test different approaches—some may be faster depending on your schema and engine.

基本上就這些。

以上是如何在SQL中從寬到長(zhǎng)格式中取消分散數(shù)據(jù)？的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！