️ Code Snippet — July 19, 2026 ## Pivot Rows to Columns with Dynamic Colum

🗃️ Code Snippet — July 19, 2026

Pivot Rows to Columns with Dynamic Column Generation (SQL)

When you have key-value pairs (survey responses, product attributes, student grades) and need rows as columns.

Static pivot (know your columns ahead of time — works on every SQL dialect):

SELECT
    respondent_id,
    MAX(CASE WHEN question = 'Name'   THEN answer END) AS Name,
    MAX(CASE WHEN question = 'Age'    THEN answer END) AS Age,
    MAX(CASE WHEN question = 'City'   THEN answer END) AS City,
    MAX(CASE WHEN question = 'Rating' THEN answer END) AS Rating
FROM survey_responses
GROUP BY respondent_id;

Numeric aggregation with COALESCE (avoids NULLs):

SELECT
    region,
    COALESCE(SUM(CASE WHEN month='2025-01' THEN revenue END), 0) AS "Jan",
    COALESCE(SUM(CASE WHEN month='2025-02' THEN revenue END), 0) AS "Feb",
    COALESCE(SUM(revenue), 0) AS "Total"
FROM sales_by_month
GROUP BY region;

Dynamic pivot (MySQL — auto-detects columns):

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN question=''', question,
           ''' THEN answer END) AS `', question, '`')
    ORDER BY question SEPARATOR ', '
) INTO @sql FROM survey_responses;
SET @full = CONCAT('SELECT respondent_id, ', @sql,
    ' FROM survey_responses GROUP BY respondent_id');
PREPARE stmt FROM @full;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

💡 For PostgreSQL swap GROUP_CONCAT → STRING_AGG. The MAX(CASE WHEN...) pattern works on literally every SQL database. Wrap in COALESCE(..., 0) to avoid NULLs.