如何编写SQL查询将多College列转换为单值多行目标结构？

Got it, let's work through this problem: you have a single row with id, college1, college2, college3 and need to turn it into three rows—each keeping the same id and holding one of the college values in a new college column. Here's how to do it, with options for different SQL databases.

Universal Solution (Works in Almost All Databases) #

The most straightforward, cross-database approach uses UNION ALL to combine three separate select statements. Each statement pulls the id and maps one of the original collegeX columns to the new college field.

SELECT id, college1 AS college
FROM your_table
WHERE id = 1 -- Remove this line if you want to apply this to all rows in the table
UNION ALL
SELECT id, college2 AS college
FROM your_table
WHERE id = 1
UNION ALL
SELECT id, college3 AS college
FROM your_table
WHERE id = 1;

Quick Breakdown: #

• Each SELECT targets the same id and renames one college column to college.
• UNION ALL stacks these results into a single set (we use ALL here because we don't need to remove duplicates, which saves performance).
• If you need to unpivot every row in your table, just delete the WHERE id = 1 clauses from each query.

Database-Specific Shortcuts #

If you're using a database with built-in unpivoting tools, you can write more concise code:

SQL Server / Azure SQL #

Use the native UNPIVOT operator:

SELECT id, college
FROM your_table
UNPIVOT (
  college FOR college_columns IN (college1, college2, college3)
) AS unpivoted_data
WHERE id = 1;

PostgreSQL #

Leverage array functions with unnest to expand the college columns into rows:

SELECT id, unnest(array[college1, college2, college3]) AS college
FROM your_table
WHERE id = 1;

MySQL 8.0+ #

Use JSON_TABLE to unpivot via JSON array conversion:

SELECT t.id, j.college
FROM your_table t
JOIN JSON_TABLE(
  JSON_ARRAY(t.college1, t.college2, t.college3),
  '$[*]' COLUMNS (college VARCHAR(255) PATH '$')
) j
WHERE t.id = 1;

All these methods will output exactly what you need: 3 rows, each with id=1 and one of the values abc, xyz, rst in the college column.

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