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.

Community · Accepted Answer · 2017-05-23 12:12:11Z

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, 2017 at 12:12

CommunityBot

11 silver badge

answered Dec 6, 2012 at 13:22

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

6 Comments

Julian Over a year ago

thanks for the quick answer, but when I tried this crosstab function it gave me an error - "ERROR: function crosstab(unknown) does not exist"

Erwin Brandstetter Over a year ago

@dna: You need to install the additional module tablefunc. Follow my link and read my answer there.

Julian Over a year ago

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

Erwin Brandstetter Over a year ago

@dna I presented two alternatives. The linked question has answers for both.

Julian Over a year ago

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.

|

Community · Accepted Answer · 2017-05-23 11:55:57Z

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, 2017 at 11:55

CommunityBot

11 silver badge

answered Dec 6, 2012 at 13:17

Ben

35.8k6 gold badges81 silver badges117 bronze badges

3 Comments

Erwin Brandstetter Over a year ago

This is tagged [PostgreSQL], there is no PIVOT keyword. You may be thinking of SQL Server.

Ben Over a year ago

@ErwinBrandstetter, That is why the poor-man's pivot in the link.

Ben Over a year ago

@ErwinBrandstetter, ok it was linked from the one I linked from!

Collectives™ on Stack Overflow

Convert multiple rows into one row with multiple columns

2 Answers 2

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related