SQL Workbench中实现列数据转行（Pivot）的SQL语句咨询

Hey Garrett, totally get you're trying to restructure your population data table in SQL Workbench—let's work through this! To give you the exact SQL statement you need, I’ll just need a few more details from you first:

• The full structure of your current table (column names, data types, and maybe a sample of the data)
• The exact structure you want for your target table (again, column names, data types, and a sample of what the final data should look like)

That said, to help you get started, I’ll walk through a couple of common population table conversion scenarios that might match your use case.

Example 1: Converting a "Wide" Table to a "Long" Table #

Suppose your current table stores population data for multiple years as separate columns (a wide table):

-- Current table structure (example)
CREATE TABLE current_population (
    region VARCHAR(50),
    2020_pop INT,
    2021_pop INT,
    2022_pop INT
);

And your target table wants each year’s population as a separate row (a long table):

-- Target table structure (example)
CREATE TABLE target_population (
    region VARCHAR(50),
    year INT,
    population INT
);

To convert this, you can use UNION ALL to unpivot the data:

-- Insert transformed data into target table
INSERT INTO target_population (region, year, population)
SELECT region, 2020 AS year, 2020_pop AS population FROM current_population
UNION ALL
SELECT region, 2021 AS year, 2021_pop AS population FROM current_population
UNION ALL
SELECT region, 2022 AS year, 2022_pop AS population FROM current_population;

Example 2: Converting a "Long" Table to a "Wide" Table #

If your current table stores each year’s population as a separate row (long table):

-- Current table structure (example)
CREATE TABLE current_population (
    region VARCHAR(50),
    year INT,
    population INT
);

And your target table wants years as separate columns (wide table):

-- Target table structure (example)
CREATE TABLE target_population (
    region VARCHAR(50),
    2020_pop INT,
    2021_pop INT,
    2022_pop INT
);

For MySQL (which SQL Workbench often connects to), you can use CASE WHEN with aggregation to pivot the data:

-- Insert transformed data into target table
INSERT INTO target_population (region, 2020_pop, 2021_pop, 2022_pop)
SELECT
    region,
    MAX(CASE WHEN year = 2020 THEN population END) AS 2020_pop,
    MAX(CASE WHEN year = 2021 THEN population END) AS 2021_pop,
    MAX(CASE WHEN year = 2022 THEN population END) AS 2022_pop
FROM current_population
GROUP BY region;

If you’re using a different database (like SQL Server), the syntax would use the PIVOT operator instead—but let me know which one you’re working with!

Once you share your actual table structures and data samples, I can tweak this to fit your exact needs.

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