SQL Server中如何将单行多列值转换为两列多行？

I've got you covered on this transpose task—turning that single row with 4 columns into a two-column table where each row maps the original column name to its value. Below are practical approaches for common tools you might be using:

SQL Approach #

If you're working with a database, the method varies slightly by system, but the core idea is to "unpivot" the single row into multiple rows.

For MySQL, SQLite, or basic SQL (using UNION ALL) #

This is a straightforward method that works across most SQL databases:

CREATE TABLE Table01_T AS
SELECT 'First' AS Column01, First AS Column02 FROM Table01
UNION ALL
SELECT 'Second' AS Column01, Second AS Column02 FROM Table01
UNION ALL
SELECT 'Third' AS Column01, Third AS Column02 FROM Table01
UNION ALL
SELECT 'Fourth' AS Column01, Fourth AS Column02 FROM Table01;

This explicitly maps each original column to a row in the new table, and it'll work every time you have a single row in Table01.

For SQL Server (using UNPIVOT) #

If you prefer a more concise syntax, UNPIVOT is built for this:

SELECT Column01, Column02
INTO Table01_T
FROM Table01
UNPIVOT (
    Column02 FOR Column01 IN (First, Second, Third, Fourth)
) AS UnpivotTable;

For PostgreSQL (using JSON functions) #

PostgreSQL's JSON handling makes this clean:

CREATE TABLE Table01_T AS
SELECT key AS Column01, value AS Column02
FROM Table01, json_each_text(to_json(Table01));

Excel Power Query Approach #

If you're working in Excel, Power Query makes this a click-and-go process:

• Select your single-row table (Table01)
• Go to the Data tab > From Table/Range to open the Power Query Editor
• In the editor, select all four columns (First, Second, Third, Fourth)
• Go to Transform tab > Unpivot Columns (or right-click the columns and select Unpivot Columns)
• Rename the auto-generated columns:
  • Rename "Attribute" to Column01
  • Rename "Value" to Column02
• Click Close & Load to save this as Table01_T in your workbook

This method will automatically update the transposed table whenever you refresh the source data.

Python Pandas Approach #

If you're using Python for data processing:

import pandas as pd

# Load your single-row data into a DataFrame
df = pd.DataFrame([{"First": "01", "Second": "02", "Third": "03", "Fourth": "04"}], 
                  columns=["First", "Second", "Third", "Fourth"])

# Transpose to two-column format
df_transposed = df.melt(var_name="Column01", value_name="Column02")

# Save to a new table (example: CSV or database)
df_transposed.to_csv("Table01_T.csv", index=False)
# For database storage: df_transposed.to_sql("Table01_T", your_database_connection)

All these methods will reliably convert your single-row 4-column input into the two-column structure you need, every time you run them.

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