Transpose sql query result - Posgresql

Question

I've a below result set.

Id Name Age
1  abc  3

Required Result:

Id 1
Name abc
Age 3

You can't do that in SQL (because a directly transposed resultset cannot be meaningfully represented in SQL: SQL only supports resultsets of identically-typed tuples, but a transposed ordered set of typed tuples cannot be represented by another set of tuples (with their own corresponding same-type) because each output row (tuple) will need its own types (e.g. your 2nd row's value is 'abc' but the others are int values. Anyway, the human-readable display of a resultset is a presentation layer concern, so you should implement transposition in your PHP/cshtml/report-generator tool instead. — Dai
– Dai, Commented Apr 15, 2023 at 7:22
Thanks @dai, I could able to achieve using other way. Thought, there might be a way to do it using SQL. — Aamir Khan
– Aamir Khan, Commented Apr 15, 2023 at 7:37
Some SQL implementations (like SQL Server) support PIVOT/UNPIVOT - and Postgres has crosstab (which isn't enabled by default) - but in both cases they only support tranformations of tables such that all output rows use the same output columns (name and type). — Dai
– Dai, Commented Apr 15, 2023 at 8:14

GMB · Accepted Answer · 2023-04-15 08:22:02Z

1

You can unpivot in a lateral join - but as mentionned by Dai in the comments, this requires casting all values to the same datatype. So:

select x.*
from mytable t
cross join lateral ( values
    ( 'Id',   id::text ),
    ( 'Name', name     ),
    ( 'Age',  age::text)
) x(col, val)

answered Apr 15, 2023 at 8:22

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Answer 1