SQL实现多行转多列：将重复ID对应的Value转为多列字段

Got it, let's work through this row-to-column pivot task you need. Since you’ve got 1500 unique IDs and up to 84 unique Values, we’ll focus on dynamic solutions that scale instead of hardcoding every single column—way easier for your dataset size. Below are tailored solutions for the most common SQL databases, plus tips for editing the pivoted data and importing it back into SQL later:

MySQL/MariaDB Solution #

MySQL doesn’t have a built-in pivot function, so we’ll use dynamic SQL with GROUP_CONCAT and conditional aggregation to generate the columns automatically:

-- Step 1: Generate dynamic column definitions
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Value = ''',
      Value,
      ''' THEN Value END) AS `Value',
      ROW_NUMBER() OVER (ORDER BY Value),
      '`'
    )
  ) INTO @sql
FROM your_table_name;

-- Step 2: Build and execute the full pivot query
SET @sql = CONCAT('SELECT ID, ', @sql, ' FROM your_table_name GROUP BY ID ORDER BY ID;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This assigns a sequential number to each unique Value (creating Value1, Value2, etc.) and uses MAX(CASE...) to pull the corresponding value for each ID, grouping rows by ID to get one row per ID.

SQL Server Solution #

SQL Server has a native PIVOT function, and we’ll pair it with dynamic SQL to handle all 84 possible columns:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- Generate the list of pivot columns (Value1, Value2, ...)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME('Value' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value) AS VARCHAR(10))) 
                    FROM your_table_name
                    GROUP BY ID, Value
                    ORDER BY ID, Value
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

-- Build and run the pivot query
SET @query = N'SELECT ID, ' + @cols + N' 
            FROM 
            (
                SELECT ID, Value,
                       ''Value'' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value) AS VARCHAR(10)) AS col_name
                FROM your_table_name
            ) x
            PIVOT 
            (
                MAX(Value)
                FOR col_name IN (' + @cols + N')
            ) p 
            ORDER BY ID;';

EXEC sp_executesql @query;

We first label each Value entry per ID with a sequential column name, then use PIVOT to rotate those labels into actual columns.

PostgreSQL Solution #

PostgreSQL uses the crosstab function from the tablefunc extension. First enable the extension, then run a dynamic pivot query:

-- Enable the required extension (run once)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Generate and execute the dynamic crosstab query
WITH value_order AS (
    SELECT DISTINCT Value, ROW_NUMBER() OVER (ORDER BY Value) AS rn
    FROM your_table_name
),
cols AS (
    SELECT string_agg(quote_ident('Value' || rn), ', ') AS col_list,
           string_agg('''' || Value || '''::text', ', ') AS value_list
    FROM value_order
)
SELECT format(
    'SELECT * FROM crosstab(
        ''SELECT ID, Value, Value FROM your_table_name ORDER BY ID, Value'',
        ''SELECT unnest(''{%s}''::text[])''
    ) AS ct(ID int, %s);',
    value_list, col_list
) INTO @sql
FROM cols;

EXECUTE @sql;

The crosstab function takes two queries: one for your source data, and another defining all possible Value values to pivot into columns.

Tips for Editing & Importing Back #

1. Export the pivoted data: Run the query above in your SQL client, then export the result as a CSV or Excel file—this gives you the easy-to-edit format you need.
2. Editing best practices: When adding new values, keep the column structure consistent. If you add a completely new Value, re-run the pivot query first to include the new column before editing.
3. Unpivot to import back: Once you’ve edited the data, you’ll need to convert it back to the original row-based format. For example, in MySQL (use similar logic for other databases):

   -- Assuming your edited data is in a temp table called edited_pivot
   SELECT ID, Value1 AS Value FROM edited_pivot WHERE Value1 IS NOT NULL
   UNION ALL
   SELECT ID, Value2 AS Value FROM edited_pivot WHERE Value2 IS NOT NULL
   -- Repeat for Value3 through Value84
   UNION ALL
   SELECT ID, Value84 AS Value FROM edited_pivot WHERE Value84 IS NOT NULL
   ORDER BY ID;
   

   You can generate this UNION ALL query dynamically (like we did for the pivot) to avoid hardcoding all 84 columns.

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