如何在PostgreSQL中将一行4列数据拆分为多行？

Hey there, I’ve run into similar scenarios before, so let’s break down how to solve this efficiently using pure SELECT statements.

Your goal is to take a single row (like A=1, B=2, C=3, D=4) and turn it into 4 rows where each row retains the first N columns (N from 1 to 4) and sets the rest to NULL. Here are two solid approaches tailored to different scenarios:

Approach 1: Using UNION ALL (Great for Small Column Counts) #

This method is straightforward and easy to read, perfect when you only have a few columns to handle. Since we’re pulling non-duplicate rows from the same source, UNION ALL is far more efficient than UNION (it skips duplicate checking, saving significant performance overhead).

-- Replace test_table with your actual table name
SELECT a, NULL AS b, NULL AS c, NULL AS d
FROM test_table
WHERE a = 1 -- Add this WHERE clause to target a specific row; remove it to process all rows
UNION ALL
SELECT a, b, NULL AS c, NULL AS d
FROM test_table
WHERE a = 1
UNION ALL
SELECT a, b, c, NULL AS d
FROM test_table
WHERE a = 1
UNION ALL
SELECT a, b, c, d
FROM test_table
WHERE a = 1;

Each SELECT block maps directly to one of your desired rows: the first keeps only column A, the second keeps A+B, the third keeps A+B+C, and the final one retains all columns.

Approach 2: Using generate_series (Better for Many Columns) #

If you have more than just 4 columns, writing multiple UNION ALL blocks gets tedious. This dynamic approach uses CROSS JOIN with generate_series to create the required number of rows, then uses CASE statements to conditionally include columns based on a row index.

SELECT
    CASE WHEN s.n >= 1 THEN a ELSE NULL END AS a,
    CASE WHEN s.n >= 2 THEN b ELSE NULL END AS b,
    CASE WHEN s.n >= 3 THEN c ELSE NULL END AS c,
    CASE WHEN s.n >= 4 THEN d ELSE NULL END AS d
FROM test_table
CROSS JOIN generate_series(1, 4) AS s(n)
WHERE a = 1; -- Adjust or remove WHERE to target specific rows or all rows

• generate_series(1,4) creates 4 rows with index values 1 through 4.
• CROSS JOIN attaches these 4 index rows to each row in your original table.
• The CASE checks if the current index n is greater than or equal to the column’s position (1 for A, 2 for B, etc.) — if yes, use the original column value; otherwise, use NULL.

This scales beautifully: if you had 10 columns, just change generate_series(1,4) to generate_series(1,10) and add corresponding CASE lines for each new column.

Quick Performance Tips #

• Both methods use pure SELECT and avoid unnecessary overhead.
• UNION ALL is slightly faster for very small column counts, but generate_series is way more maintainable as column numbers grow.
• If you’re processing the entire table, removing the WHERE clause will apply this transformation to every row in your table.

内容的提问来源于stack exchange，提问作者Maruko