如何使用SQL实现包含多列的透视表（Pivot Table）？

Hey there! Let's work through your pivot table problem. First, let's unpack why your current SQL isn't giving you the desired result, then we'll fix it with a proper pivot approach that matches your Python pivot_table output.

What's Wrong with Your Current SQL? #

Your existing query has a few key issues:

• You're only pivoting on PRODUCT, not combining it with Month (which is needed to get columns like a_1, b_2 etc.)
• The final GROUP BY ID, Month is undoing the pivot and splitting your data back into per-ID-per-Month rows, which contradicts your goal of aggregating all months per ID
• The ORDER BY value_1 is invalid because that column doesn't exist in your outer query

Static SQL Solution (Manual Columns) #

Since most SQL dialects (like SQL Server, PostgreSQL, BigQuery) require explicitly listing pivot columns for static queries, we can combine PRODUCT and Month into a single column first, then pivot on that combined value. Here's how:

WITH combined_data AS (
  SELECT
    ID,
    CONCAT(PRODUCT, '_', Month) AS product_month,
    VALUE_1
  FROM DATASET
)
SELECT
  ID,
  COALESCE(SUM(CASE WHEN product_month = 'a_1' THEN VALUE_1 END), 0) AS a_1,
  COALESCE(SUM(CASE WHEN product_month = 'a_2' THEN VALUE_1 END), 0) AS a_2,
  COALESCE(SUM(CASE WHEN product_month = 'a_3' THEN VALUE_1 END), 0) AS a_3,
  COALESCE(SUM(CASE WHEN product_month = 'a_4' THEN VALUE_1 END), 0) AS a_4,
  COALESCE(SUM(CASE WHEN product_month = 'b_1' THEN VALUE_1 END), 0) AS b_1,
  COALESCE(SUM(CASE WHEN product_month = 'b_2' THEN VALUE_1 END), 0) AS b_2,
  COALESCE(SUM(CASE WHEN product_month = 'b_3' THEN VALUE_1 END), 0) AS b_3,
  COALESCE(SUM(CASE WHEN product_month = 'b_4' THEN VALUE_1 END), 0) AS b_4,
  COALESCE(SUM(CASE WHEN product_month = 'c_1' THEN VALUE_1 END), 0) AS c_1,
  COALESCE(SUM(CASE WHEN product_month = 'c_2' THEN VALUE_1 END), 0) AS c_2,
  COALESCE(SUM(CASE WHEN product_month = 'c_3' THEN VALUE_1 END), 0) AS c_3,
  COALESCE(SUM(CASE WHEN product_month = 'c_4' THEN VALUE_1 END), 0) AS c_4,
  COALESCE(SUM(CASE WHEN product_month = 'd_1' THEN VALUE_1 END), 0) AS d_1,
  COALESCE(SUM(CASE WHEN product_month = 'd_2' THEN VALUE_1 END), 0) AS d_2,
  COALESCE(SUM(CASE WHEN product_month = 'd_3' THEN VALUE_1 END), 0) AS d_3,
  COALESCE(SUM(CASE WHEN product_month = 'd_4' THEN VALUE_1 END), 0) AS d_4
FROM combined_data
GROUP BY ID
ORDER BY ID;

If you need to include both VALUE_1 and VALUE_2 (like your Python code), you can extend the CASE statements to include both, e.g., SUM(CASE WHEN product_month = 'a_1' THEN VALUE_1 END) AS a_1_val1, SUM(CASE WHEN product_month = 'a_1' THEN VALUE_2 END) AS a_1_val2.

Dynamic SQL Solution (Auto-Generate Columns) #

If you don't want to manually list all product_month combinations (useful if products/months might change), you can use dynamic SQL to auto-generate the pivot columns. Here's an example for SQL Server:

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

-- Generate the list of product_month columns
SELECT @cols = STRING_AGG(QUOTENAME(CONCAT(PRODUCT, '_', Month)), ', ')
FROM (SELECT DISTINCT PRODUCT, Month FROM DATASET) AS unique_combinations;

-- Build the dynamic pivot query
SET @query = N'
WITH combined_data AS (
  SELECT
    ID,
    CONCAT(PRODUCT, ''_'', Month) AS product_month,
    VALUE_1
  FROM DATASET
)
SELECT ID, ' + @cols + '
FROM combined_data
PIVOT (
  SUM(VALUE_1)
  FOR product_month IN (' + @cols + ')
) AS p
ORDER BY ID;';

-- Execute the query
EXEC sp_executesql @query;

Dialects like PostgreSQL or BigQuery have their own syntax for dynamic SQL (e.g., EXECUTE in PostgreSQL), but the core idea is the same: generate the column list dynamically from distinct product-month pairs.

Answers to Your Questions #

• Do I need to run repeated queries and concatenate results? No! The above static or dynamic queries handle all product-month combinations in one go.
• Must I manually write all products? For static SQL, yes (since SQL needs to know column names at compile time). For dynamic SQL, no—you can auto-generate the columns.
• Can I achieve the desired pivot in one SQL query? Absolutely! Both the static and dynamic approaches above produce your target output in a single query.
• Did I over-apply Python's approach? A little—Python's pivot_table automates column generation behind the scenes, but SQL requires more explicit handling of pivot columns. The key adjustment is combining PRODUCT and Month into a single pivot key instead of pivoting on them separately.

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