DuckDB SQL query to generate SQL query to pivot data from rows into columns.

duckdb_generate_pivot_query.sql

WITH gen_series AS (
  SELECT 
    i, 
    count(*) over () as rows 
  FROM generate_series(1,100000) tbl(i)
), gen_year AS (
  SELECT 
    i,
    CASE WHEN i <= rows * 0.25 THEN 2022 
         WHEN i <= rows * 0.5 THEN 2023 
         WHEN i <= rows * 0.75 THEN 2024 
         WHEN i <= rows * 0.875 THEN 2025
         ELSE NULL 
         END as year 
    FROM  gen_series
)
SELECT 
  CONCAT('SELECT COUNT(i) AS total_rows, ', 
          STRING_AGG(DISTINCT CONCAT(' COUNT(i) FILTER (WHERE year ', 
                                     IF(year IS NOT NULL, CONCAT('= ', year), 'IS NULL'),
                                     ') AS ',
                                     IF(year IS NOT NULL, CONCAT('"',year,'"'), 'NULLs'))
                    ),
         ' FROM gen_year') AS query 
FROM gen_year
 
/* 
SELECT 
    COUNT(i) AS total_rows, 
    COUNT(i) FILTER (WHERE year = 2022) AS "2022",
    COUNT(i) FILTER (WHERE year = 2023) AS "2023", 
    COUNT(i) FILTER (WHERE year = 2024) AS "2024", 
    COUNT(i) FILTER (WHERE year = 2025) AS "2025", 
    COUNT(i) FILTER (WHERE year IS NULL) AS NULLs 
FROM gen_year
*/