ask for pivot feature

[l1t1]

please add the pivot/unpivot statement in clickhouse.

Discussed in #50473

^{Originally posted by l1t1 June 2, 2023}
oracle, duckdb
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#SQLRF01702
https://duckdb.org/docs/sql/statements/pivot

[l1t1]

duckdb design
duckdb/duckdb#6961 (comment)

[Pipboyguy]

Snowflake has this as well https://docs.snowflake.com/en/sql-reference/constructs/pivot

[danthegoodman1]

Bumping as this is super useful and painful without

[alanpaulkwan]

+1 +1 +1

[anjmao]

Aggregate combinators seems to solve pivot https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states

SELECT
    countIf((status = 'confirmed') AND (confirm_time > (create_time + toIntervalMinute(1)))) AS num_confirmed_checked,
    sumIf(total_amount, (status = 'confirmed') AND (confirm_time > (create_time + toIntervalMinute(1)))) AS confirmed_checked_amount,
    countIf(status = 'declined') AS num_declined,
    sumIf(total_amount, status = 'declined') AS dec_amount,
    avgIf(total_amount, status = 'declined') AS dec_average
FROM payments

┌─num_confirmed_checked─┬─confirmed_checked_amount─┬─num_declined─┬────────dec_amount─┬───────dec_average─┐
│                    39 │        11195.98991394043 │           50 │ 10780.18000793457 │ 215.6036001586914 │
└───────────────────────┴──────────────────────────┴──────────────┴───────────────────┴───────────────────┘

[alanpaulkwan]

@anjmao doesn't this require manually enumerating values? What if I had 50 distinct values

[rnesytov]

I've tried solution with argMaxIf (get last values by created_at for specific column value) with 200+ values/columns. Got an issue with memory usage, every new value/column requires more and more memory.
So, one could you this solution with reasonable amount of columns.