CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstabunnest
Gopal
Gopal

Reputation: 1452

Postgresql change column to row in a generic way

A key value pair(column name, value) needs to be retrieved from a table with about hundred columns.

The following query does exactly what is needed.

With dummy AS (
    Select 1 as Col1, 2 as Col2
)
SELECT
   unnest(array['Col1', 'Col2']) AS "Column Name",
   unnest(array[Col1, Col2]) AS "Value"
FROM dummy

But, it requires to specify the columns explicitly.

Is there a way to generate the same output by not specifying the column names directly at all or generate it dynamically.

Upvotes: 0

Views: 727

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324305

Yes, within limits. See the crosstab function in the tablefunc extension.

It's not as nice as a real native CROSSTAB or PIVOT command, but it'll do the job.

Upvotes: 1

Related Questions