I’ve been finding excuses to reach for DuckDB instead of Python.

Today I learned about UNPIVOT in DuckDB. It reshapes the data from “wide” to “long”.

I had a data set that looked something like this:

Employee ID	Hire Date	Type	Country	Pay Group
1234	2026-01-01	Regular	United States	Weekly

And I needed the data set to look like this:

ID	Field Name	Field Value	Effective Date	Copy to Default	Copy to RLS
1234	Type	Regular	2026-01-01
1234	Country	United States	2026-01-01
1234	Pay Group	Weekly	2026-01-01

Copy + paste transposed in Excel wouldn’t have worked for this use case (at least not easily).

In DuckDB, I could read, transform, and export the data with a little AI prompting help.

COPY (
    SELECT
        "Employee ID" AS ID,
        field_name AS "Field Name",
        field_value AS "Field Value",
        "Hire Date" AS "Effective Date",
        '' AS "Copy to Default",
        '' AS "Copy to RLS"
    FROM read_csv('~/Downloads/input.csv', all_varchar=true)
    UNPIVOT INCLUDE NULLS (
        field_value FOR field_name IN (
            COLUMNS(* EXCLUDE ("Employee ID", "Hire Date"))
        )
    )
)
TO '~/Downloads/output.csv' (HEADER, DELIMITER ',');

• COPY: Exports (copies) the transformed data TO a CSV file
• FROM read_csv: Reads the data from the input CSV file. Also sets all the field types to VARCHAR for compatibility with UNPIVOT which requires the unpivoted columns to share a type
• UNPIVOT: Stacks multiple columns into fewer columns.
• INCLUDE NULLS: UNPIVOT skips nulls by default. This includes blank/null data fields from the original CSV.
• field_value FOR field_name IN (COLUMNS(* EXCLUDE ("..",".."))): Defines the fields to be stacked into fewer columns + specifying all the columns except for ("..","..")