Postgresql change column to row in a generic way

Question

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.

Craig Ringer · Accepted Answer · 2013-07-19 12:48:23Z

1

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.

answered Jul 19, 2013 at 12:48

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql change column to row in a generic way

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related