技术问询：如何通过Pivoting将两个值合并至同一列

Hey there! When you need to pivot two values into one column, it usually falls into one of two common scenarios—either you're unpivoting multiple columns into a single column of rows (the most frequent use case folks mean by this) or you're aggregating two row values into a single column cell. Let's break both down with concrete SQL examples, since that's where this operation comes up most often.

Scenario 1: Unpivoting Two Columns Into One Column of Rows #

Say you have a table with two separate columns holding related values, and you want to stack them into a single column while keeping their associated ID or key. For example, let's start with this sample data:

CREATE TABLE sample_data (
    id INT,
    value_a INT,
    value_b INT
);

INSERT INTO sample_data VALUES (1, 10, 20), (2, 30, 40);

We want to turn the two value_ columns into a single combined_value column, with each ID having two rows (one for each original value).

Method 1: Using UNION ALL (works in most SQL dialects) #

This is the simplest, most universal approach—we select each column separately and stack the results:

SELECT id, value_a AS combined_value FROM sample_data
UNION ALL
SELECT id, value_b AS combined_value FROM sample_data
ORDER BY id;

Use UNION instead of UNION ALL if you need to remove duplicate values, but UNION ALL is faster when duplicates are acceptable.

Method 2: Using UNPIVOT (for SQL Server, Oracle, etc.) #

If your SQL dialect supports the UNPIVOT operator, you can achieve this in a single select statement:

SELECT id, combined_value
FROM sample_data
UNPIVOT (
    combined_value FOR value_type IN (value_a, value_b)
) AS unpivoted_data;

The value_type column (optional if you omit it) will track which original column each value came from (e.g., "value_a" or "value_b").

Scenario 2: Aggregating Two Row Values Into a Single Column Cell #

Sometimes you might want to take two values from separate rows (linked to the same ID, for example) and combine them into a single cell in one column. This is more of an aggregation than a traditional pivot, but it's often grouped under the same question. Let's use this sample data:

CREATE TABLE sample_rows (
    id INT,
    category VARCHAR(10),
    value INT
);

INSERT INTO sample_rows VALUES (1, 'A', 10), (1, 'B', 20), (2, 'A', 30), (2, 'B', 40);

We want to merge the two values for each ID into a single comma-separated entry in one column.

Method: Using String Aggregation Functions #

The exact function depends on your SQL dialect:

• PostgreSQL: Use STRING_AGG

SELECT id, STRING_AGG(value::VARCHAR, ', ') AS combined_value
FROM sample_rows
GROUP BY id;

• SQL Server: Use STRING_AGG (2017+) or STUFF with FOR XML PATH for older versions

-- For SQL Server 2017+
SELECT id, STRING_AGG(value, ', ') AS combined_value
FROM sample_rows
GROUP BY id;

-- For older SQL Server versions
SELECT 
    id,
    STUFF((SELECT ', ' + CAST(value AS VARCHAR) 
           FROM sample_rows sr2 
           WHERE sr2.id = sr1.id 
           FOR XML PATH('')), 1, 2, '') AS combined_value
FROM sample_rows sr1
GROUP BY id;

• MySQL: Use GROUP_CONCAT

SELECT id, GROUP_CONCAT(value SEPARATOR ', ') AS combined_value
FROM sample_rows
GROUP BY id;

Whichever scenario fits your use case, the key is to match the method to your database system and the structure of your source data. Feel free to tweak these examples if you have edge cases or use a different tool!

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