sqlpostgresqlunpivotlateral-join

Transpose sql query result - Posgresql


I've a below result set.

Id Name Age
1  abc  3

Required Result:

Id 1
Name abc
Age 3

Solution

  • 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)