Transposing columns to rows.

You have already probably done some crosstab query from your favorite RDBMS, i.e. the rows are transposed to columns.

But how about a reverse crosstab(a.k.a. reverse pivot)? You got columns that you wanted to transpose to rows.

That is, you need to display this...

testdb-# 

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select * from p;


 person_id | lastname  |  firstname   | nickname | favorite_number |       fullname       
-----------+-----------+--------------+----------+-----------------+----------------------
         1 | lennon    | john winston | john     |                 | john winston lennon
         2 | mccartney | james paul   | paul     |                 | james paul mccartney
         3 | harrison  | george       |          |                 | george harrison
         4 | starr     | richard      | ringo    |              10 | richard starr
(4 rows)

...as this:

person_id  |      field      |        value         
-----------+-----------------+----------------------
         1 | favorite_number | 
         1 | firstname       | john winston
         1 | fullname        | john winston lennon
         1 | lastname        | lennon
         1 | nickname        | john
         1 | person_id       | 1
         2 | favorite_number | 
         2 | firstname       | james paul
         2 | fullname        | james paul mccartney
         2 | lastname        | mccartney
         2 | nickname        | paul
         2 | person_id       | 2
         3 | favorite_number | 
         3 | firstname       | george
         3 | fullname        | george harrison
         3 | lastname        | harrison
         3 | nickname        | 
         3 | person_id       | 3
         4 | favorite_number | 10
         4 | firstname       | richard
         4 | fullname        | richard starr
         4 | lastname        | starr
         4 | nickname        | ringo
         4 | person_id       | 4
(24 rows)

On other RDBMS, you got to do this query:

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select 
    person_id,
    'firstname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'firstname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'lastname' as field,
    firstname as value
from p
union all
select 
    person_id,
    'nickname' as field,
    nickname as value
from p
union all
select 
    person_id,
    'fullname' as field,
    fullname as value
from p
union all
select 
    person_id,
    'favorite_number' as field,
    favorite_number::text as value
from p
order by person_id, field

That's very tedious.


If you are using Postgres, luckily there is a better way:

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select
    person_id,
    skeys(hstore(p)) as field, svals(hstore(p)) as value 
from p
order by person_id, field

That's more maintainable, even if you add another column on the main query, your key value pair query need not be modified.



There's another approach (darn, PostgreSQL is too flexible! :D)

with p as
(
    select *, firstname || ' ' || lastname as fullname 
    from person 
)
select 
    p.person_id,
    unnest(array['person_id', 'favorite_number', 'firstname','fullname','lastname', 'nickname']) as field_label,
    unnest(array[cast(p.person_id as text), cast(p.favorite_number as text), p.firstname, p.fullname, p.lastname, p.nickname])  as field_value
from p
order by p.person_id, field_label

That's it folks. Happy Computing! ツ


Data Source:

create table person
(
 person_id serial not null primary key,
 lastname text not null,
 firstname text not null,
 nickname text null,
 favorite_number int null
);
 
 
insert into person(lastname,firstname,nickname, favorite_number) values
('lennon','john winston','john',default),
('mccartney','james paul','paul',default),
('harrison','george',default,default),
('starr','richard','ringo', 10);