CODEWITHSUNDEEP
sqlpostgresqlcrosstab
Julian
Julian

Reputation: 393

Convert multiple rows into one row with multiple columns

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.

Upvotes: 1

Views: 6034

Answers (2)

Ben
Ben

Reputation: 35613

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:

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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

Upvotes: 1

Related Questions