Oracle SQL动态列行转列（Pivot）问题求助

Hey there! Let's fix that pivot issue you're stuck on. The problem with your original query is that most SQL dialects (like SQL Server) don’t support dynamic column lists directly in the PIVOT’s IN clause—you can’t just throw a subquery in there. Instead, we need to build the query dynamically to account for the varying number of problems per vehicle-date pair.

Step 1: Assign Row Numbers to Problems per Group #

First, we need to number each problem within its Vehicle_ID + Date group. This gives us the structure for our Problem_1, Problem_2, etc. columns:

SELECT 
    Vehicle_ID,
    Date,
    Problem,
    -- Create a numbered column name for each problem in the group
    'Problem_' + CAST(ROW_NUMBER() OVER (PARTITION BY Vehicle_ID, Date ORDER BY Problem) AS VARCHAR(10)) AS Problem_Column
FROM table1

This will output each problem with a unique column identifier tied to its position in the vehicle-date group.

Step 2: Build and Execute Dynamic Pivot Query #

Next, we’ll generate the list of dynamic columns and plug them into a pivot query. Here’s a complete example for SQL Server:

DECLARE @columnList NVARCHAR(MAX);
DECLARE @dynamicPivotQuery NVARCHAR(MAX);

-- Generate the list of Problem_1, Problem_2, ... columns
SELECT @columnList = STRING_AGG(QUOTENAME(Problem_Column), ', ')
FROM (
    SELECT DISTINCT 
        'Problem_' + CAST(ROW_NUMBER() OVER (PARTITION BY Vehicle_ID, Date ORDER BY Problem) AS VARCHAR(10)) AS Problem_Column
    FROM table1
) AS Columns;

-- Build the full pivot query
SET @dynamicPivotQuery = N'
SELECT Vehicle_ID, Date, ' + @columnList + '
FROM (
    SELECT 
        Vehicle_ID,
        Date,
        Problem,
        ''Problem_'' + CAST(ROW_NUMBER() OVER (PARTITION BY Vehicle_ID, Date ORDER BY Problem) AS VARCHAR(10)) AS Problem_Column
    FROM table1
) AS SourceData
PIVOT (
    MAX(Problem)  -- MAX works here since each Problem_Column maps to one value per group
    FOR Problem_Column IN (' + @columnList + ')
) AS PivotResult;';

-- Run the dynamic query
EXEC sp_executesql @dynamicPivotQuery;

What This Does: #

1. STRING_AGG (use STUFF + FOR XML PATH if you’re on an older SQL Server version) combines all the dynamic Problem_X column names into a comma-separated string.
2. We inject this column list into a pivot query that uses the numbered problem columns we created earlier.
3. Executing the dynamic query gives you exactly the output you want: one row per Vehicle_ID + Date, with problems spread across Problem_1 to Problem_N columns.

Why Your Original Query Failed #

Your initial PIVOT attempt tried to use (SELECT DISTINCT Problem FROM table1) in the IN clause, but SQL requires explicit column names here—subqueries won’t work. Dynamic SQL gets around this by building the column list at runtime based on your actual data.

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