Convert multiple rows into one row with multiple columns

Question

I've a table containing information as follows:

idnumber     applic_id      cctype     ccnumber
---------  ------------    --------   ----------
    1           23            1         223445
    2           23            2         345567

I need a query that an make this:

idnumber     applic_id      cctype     ccnumber  idnumber     applic_id      cctype     ccnumber
---------  ------------    --------   ----------  ---------  ------------    --------   ----------
    1           23            1         223445       2           23            2         345567

Is anyone have a clue? I'm using PostgreSQL 8.3.

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

You can use CASE statements for simple queries.
Or use the crosstab() function of the tablefunc module for more complex cases and better performance.

You can find examples for both cases under this related question:
PostgreSQL Crosstab Query

edited May 23 '17 at 12:12

Community

1
1

answered Dec 06 '12 at 13:22

Erwin Brandstetter

605,456
145
1,078
1,228

thanks for the quick answer, but when I tried this crosstab function it gave me an error - "ERROR: function crosstab(unknown) does not exist" – Julian Dec 06 '12 at 14:13
@dna: You need to install the additional module `tablefunc`. Follow my link and read my answer there. – Erwin Brandstetter Dec 06 '12 at 14:21
I can't install anything because I'm not administrator on the server :) that is why I'll have to find another way to handle the problem :) thanks for the replay anyway :) – Julian Dec 06 '12 at 14:39
@dna I presented *two* alternatives. The linked question has answers for *both*. – Erwin Brandstetter Dec 06 '12 at 14:59
Sorry but I'm new in this forum, and I can't find the answers in the link you presented, it just opens your profile. – Julian Dec 06 '12 at 15:16
@dna: That's my profile! :D The last line in my answer is a link! [PostgreSQL Crosstab Query](http://stackoverflow.com/questions/3002499/postgresql-crosstab-query) – Erwin Brandstetter Dec 06 '12 at 16:29

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

This is known as a PIVOT.

You can do it either with the PIVOT keyword which does not exist in PostgreSQL, or using the poor-man's pivot like this:

Poor Man's Pivot:

edited May 23 '17 at 11:55

Community

1
1

answered Dec 06 '12 at 13:17

Ben

34,935
6
74
113

This is tagged [PostgreSQL], there is no `PIVOT` keyword. You may be thinking of SQL Server. – Erwin Brandstetter Dec 06 '12 at 13:18
@ErwinBrandstetter, That is why the poor-man's pivot in the link. – Ben Dec 06 '12 at 16:14
@ErwinBrandstetter, ok it was linked from the one I linked from! – Ben Dec 06 '12 at 16:16

Convert multiple rows into one row with multiple columns

2 Answers2

Linked