An interesting feature of relational databases(postgres in this case) is the ability to rotate the table about a pivot. So if you have data like this-
id | rowid | key | value
---+------+----+-------
1 | test1 | key1 | val1
2 | test1 | key2 | val2
3 | test1 | key3 | val3
4 | test1 | key4 | val4
5 | test2 | key1 | val5
6 | test2 | key2 | val6
7 | test2 | key3 | val7
8 | test2 | key4 | val8
And want to have a result set like this -
rowid | key1 | key2 | key3 | key4
------+------+-----+-----+------
test1 | val1 | val2 | val3 | val4
test2 | val5 | val6 | val7 | val8
It can be achieved by a "crosstab" query in a postgres database -
Update: 02/14/2013
The following can be achieved by a crosstab and also by an interesting SQL which I came across here - http://stackoverflow.com/questions/14863985/postgres-crosstab-maybe
id | rowid | key | value
---+------+----+-------
1 | test1 | key1 | val1
2 | test1 | key2 | val2
3 | test1 | key3 | val3
4 | test1 | key4 | val4
5 | test2 | key1 | val5
6 | test2 | key2 | val6
7 | test2 | key3 | val7
8 | test2 | key4 | val8
And want to have a result set like this -
rowid | key1 | key2 | key3 | key4
------+------+-----+-----+------
test1 | val1 | val2 | val3 | val4
test2 | val5 | val6 | val7 | val8
It can be achieved by a "crosstab" query in a postgres database -
Update: 02/14/2013
The following can be achieved by a crosstab and also by an interesting SQL which I came across here - http://stackoverflow.com/questions/14863985/postgres-crosstab-maybe
4 comments:
What about if you have an indeterminate number of values, e.g., key1, key2,...kn, such that you can't specify them in the cross?
What about the reverse? If you need to "un-pivot" table2 to get table1 as a result?
Not sure, if that can be done via sql. Something to look into.
Nice Article !
This is my pleasure to read your article.
Really this will help to people of PostgreSQL Community.
I have also prepared one article about, Create pivot table in PostgreSQL.
You can also visit my article, your comments and reviews are most welcome.
http://www.dbrnd.com/2016/09/postgresql-create-pivot-table-to-arrange-rows-into-columns-form-using-group-by-clause/
Post a Comment