如何将列变量用作列名？附当前SQL查询及返回结果

Hey there! It sounds like you want to transform your query results so that the distinct values from the instance_type column become actual column names, with their corresponding summed quantity values filling those columns. Let's walk through how to do this—this is a common pivot operation in SQL, and we've got a couple of approaches depending on your needs.

First, let's recap your current setup to make sure we're aligned. Your original query:

SELECT a.instance_type, SUM(a.quantity) as quantity, b.name, b.id 
FROM sales_iteminstance a 
INNER JOIN inventory_item b ON b.id = a.fk_item_id 
GROUP BY (a.instance_type, b.id) 
ORDER BY (b.id)

Returns results structured like this:

You want to restructure this into a more compact view where each item has one row, and each instance_type becomes a column:

Method 1: Static Pivot (Fixed instance_type Values) #

If you know all possible values of instance_type upfront (like Sell, Buy, Return in your example), this is the simplest, most compatible approach. We'll use CASE WHEN statements to conditionally sum quantities—this works across nearly all SQL databases.

SELECT 
    b.id,
    b.name,
    SUM(CASE WHEN a.instance_type = 'Sell' THEN a.quantity ELSE 0 END) AS Sell,
    SUM(CASE WHEN a.instance_type = 'Buy' THEN a.quantity ELSE 0 END) AS Buy,
    SUM(CASE WHEN a.instance_type = 'Return' THEN a.quantity ELSE 0 END) AS Return
FROM sales_iteminstance a 
INNER JOIN inventory_item b ON b.id = a.fk_item_id 
GROUP BY b.id, b.name  -- Group by item instead of item + instance_type
ORDER BY b.id;

How this works:

• For each instance_type, we check if the row matches that value. If yes, we include its quantity; otherwise, we use 0.
• Summing these conditional values gives us the total quantity for each type per item, grouped by the item's id and name.

If your database supports the PIVOT syntax (like SQL Server, PostgreSQL 11+, or Oracle), you can use that for cleaner, more concise code:

SQL Server Example:

SELECT id, name, Sell, Buy, Return
FROM (
    SELECT 
        b.id,
        b.name,
        a.instance_type,
        a.quantity
    FROM sales_iteminstance a 
    INNER JOIN inventory_item b ON b.id = a.fk_item_id
) AS SourceTable
PIVOT (
    SUM(quantity)
    FOR instance_type IN ([Sell], [Buy], [Return])
) AS PivotTable
ORDER BY id;

Method 2: Dynamic Pivot (Variable instance_type Values) #

If instance_type can have new values added over time and you don't want to update your query every time, you'll need dynamic SQL. This builds the pivot columns automatically based on the distinct values in your table.

Here's an example for MySQL:

-- Step 1: Generate dynamic column definitions
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT 
    CONCAT('SUM(CASE WHEN instance_type = ''', instance_type, ''' THEN quantity ELSE 0 END) AS ', instance_type)
) INTO @cols
FROM sales_iteminstance;

-- Step 2: Build the full query string
SET @query = CONCAT('
    SELECT b.id, b.name, ', @cols, '
    FROM sales_iteminstance a 
    INNER JOIN inventory_item b ON b.id = a.fk_item_id 
    GROUP BY b.id, b.name 
    ORDER BY b.id
');

-- Step 3: Execute the dynamic query
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

For PostgreSQL, the approach uses string aggregation and EXECUTE:

DO $$
DECLARE
    cols TEXT;
BEGIN
    SELECT string_agg(DISTINCT 
        format('SUM(CASE WHEN instance_type = %L THEN quantity ELSE 0 END) AS %I', instance_type, instance_type),
        ', '
    ) INTO cols
    FROM sales_iteminstance;

    EXECUTE format('
        SELECT b.id, b.name, %s
        FROM sales_iteminstance a 
        INNER JOIN inventory_item b ON b.id = a.fk_item_id 
        GROUP BY b.id, b.name 
        ORDER BY b.id
    ', cols);
END $$;

How this works:

• First, we query all distinct instance_type values and dynamically build the CASE WHEN column expressions.
• Then we insert those expressions into a full SQL query string.
• Finally, we execute the dynamically built query.

Key Notes: #

• Dynamic SQL requires appropriate database permissions, as it executes generated code.
• If instance_type can contain user-provided values, always sanitize inputs to avoid SQL injection risks.

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