use row data as columns in PostgreSQL 9.1

Question

--------------------
|bookname |author   |
--------------------
|book1    |author1  |
|book1    |author2  |
|book2    |author3  |
|book2    |author4  |
|book3    |author5  |
|book3    |author6  |
|book4    |author7  |
|book4    |author8  |
---------------------

but I want the booknames as columns and authors as its rows ex

----------------------------------
|book1  |book2   |book3  |book4  |
----------------------------------
|author1|author3 |author5|author7|
|author2|author4 |author6|author8|
----------------------------------

is it possible in postgres? How can I do this?

I tried crosstab but I failed to do this.

"I tried crosstab but I failed to do this" - crosstab is the way to go. Show us what you tried and the exact error message. — user330315
– user330315, Commented Aug 21, 2013 at 12:23

Taryn · Accepted Answer · 2013-08-21 13:37:56Z

2

You can get the result using an aggregate function with a CASE expression but I would first use row_number() so you have a value that can be used to group the data.

If you use row_number() then the query could be:

select 
  max(case when bookname = 'book1' then author end) book1,
  max(case when bookname = 'book2' then author end) book2,
  max(case when bookname = 'book3' then author end) book3,
  max(case when bookname = 'book4' then author end) book4
from
(
  select bookname, author,
    row_number() over(partition by bookname
                      order by author) seq
  from yourtable
) d
group by seq;

See SQL Fiddle with Demo. I added the row_number() so you will return each distinct value for the books. If you exclude the row_number(), then using an aggregate with a CASE will return only one value for each book.

This query gives the result:

|   BOOK1 |   BOOK2 |   BOOK3 |   BOOK4 |
-----------------------------------------
| author1 | author3 | author5 | author7 |
| author2 | author4 | author6 | author8 |

answered Aug 21, 2013 at 13:37

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

1 Comment

user2703248 Over a year ago

Thanks it works but if book numbers increases then I should have to increase this statements. max(case when bookname = 'book4' then author end) book4 is there any way to create columns dynamically??

Collectives™ on Stack Overflow

use row data as columns in PostgreSQL 9.1

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related