Skip to content

Instantly share code, notes, and snippets.

@pratikmallya

pratikmallya/gist:a8c47928a7e358ffb59d75531bdce1f1

Created March 7, 2026 13:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save pratikmallya/a8c47928a7e358ffb59d75531bdce1f1 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save pratikmallya/a8c47928a7e358ffb59d75531bdce1f1 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. pratikmallya created this gist Mar 7, 2026.
    45 changes: 45 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,45 @@
    ```
    In [59]: duckdb.sql("""
    ...: UNPIVOT (
    ...: SELECT Agency, Mode, TOS,
    ...: * EXCLUDE ("NTD ID", "Legacy NTD ID", Agency,
    ...: "Mode/Type of Service Status", "Reporter Type",
    ...: "UACE CD", "UZA Name", Mode, TOS, "3 Mode")
    ...: FROM upt
    ...: WHERE Agency = 'Fort Worth Transportation Authority' AND Mode = 'CR'
    ...: )
    ...: ON COLUMNS(* EXCLUDE (Agency, Mode, TOS))
    ...: INTO NAME month VALUE upt
    ...: """)
    Out[59]:
    ┌─────────────────────────────────────┬─────────┬─────────┬─────────┬──────────┐
    │ Agency │ Mode │ TOS │ month │ upt │
    │ varchar │ varchar │ varchar │ varchar │ double │
    ├─────────────────────────────────────┼─────────┼─────────┼─────────┼──────────┤
    │ Fort Worth Transportation Authority │ CR │ PT │ 1/2002 │ 78375.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 2/2002 │ 64771.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 3/2002 │ 76589.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 4/2002 │ 68920.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 5/2002 │ 62796.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 6/2002 │ 63173.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 7/2002 │ 80804.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 8/2002 │ 78080.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 9/2002 │ 63093.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 10/2002 │ 72580.0 │
    │ · │ · │ · │ · │ · │
    │ · │ · │ · │ · │ · │
    │ · │ · │ · │ · │ · │
    │ Fort Worth Transportation Authority │ CR │ PT │ 4/2025 │ 67832.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 5/2025 │ 72677.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 6/2025 │ 71949.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 7/2025 │ 78107.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 8/2025 │ 73379.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 9/2025 │ 72977.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 10/2025 │ 76042.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 11/2025 │ 83109.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 12/2025 │ 129115.0 │
    │ Fort Worth Transportation Authority │ CR │ PT │ 1/2026 │ 68170.0 │
    ├─────────────────────────────────────┴─────────┴─────────┴─────────┴──────────┤
    │ 193 rows (20 shown) 5 columns │
    └──────────────────────────────────────────────────────────────────────────────┘
    ```