PostgreSQL multiple row as columns

Question

I have a table like this:

| id | name | segment | date_created | question | answer |
|----|------|---------|--------------|----------|--------|
| 1  | John | 1       | 2018-01-01   | 10       | 28     |
| 1  | John | 1       | 2018-01-01   | 14       | 37     |
| 1  | John | 1       | 2018-01-01   | 9        | 83     |
| 2  | Jack | 3       | 2018-03-11   | 22       | 13     |
| 2  | Jack | 3       | 2018-03-11   | 23       | 16     |

And I want to show this information in a single row, transpose all the questions and answers as columns:

| id | name | segment | date_created | question_01 | answer_01 | question_02 | answer_02 | question_03 | answer_03 |
|----|------|---------|--------------|-------------|-----------|-------------|-----------|-------------|-----------|
| 1  | John | 1       | 2018-01-01   | 10          | 28        | 14          | 37        | 9           | 83        |
| 2  | Jack | 3       | 2018-03-11   | 22          | 13        | 23          | 16        |             |           |

The number os questions/answers for the same ID is known. Maximum of 15.

I'm already tried using crosstab, but it only accepts a single value as category and I can have 2 (question/answer). Any help how to solve this?

One option is a regular pivot query. Google "SQL pivot query" for help on that. I also think that crosstab could be made to work here. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 27, 2018 at 11:43

D-Shih · Accepted Answer · 2018-07-27 11:54:53Z

3

You can try to use row_number to make a number in subquery then, do Aggregate function condition in the main query.

SELECT  ID,
        Name,
        segment,
        date_created,
        max(CASE WHEN rn = 1 THEN question END) question_01 ,
        max(CASE WHEN rn = 1 THEN answer END) answer_01  ,
        max(CASE WHEN rn = 2 THEN question END) question_02, 
        max(CASE WHEN rn = 2 THEN answer END) answer_02,
        max(CASE WHEN rn = 3 THEN question END) question_03,
        max(CASE WHEN rn = 3 THEN answer END) answer_03
FROM (
    select *,Row_number() over(partition by ID,Name,segment,date_created order by (select 1)) rn
    from T
) t1
GROUP BY ID,Name,segment,date_created

sqlfiddle

[Results]:

| id | name | segment | date_created | question_01 | answer_01 | question_02 | answer_02 | question_03 | answer_03 |
|----|------|---------|--------------|-------------|-----------|-------------|-----------|-------------|-----------|
|  1 | John |       1 |   2018-01-01 |           1 |        28 |          14 |        37 |           9 |        83 |
|  2 | Jack |       3 |   2018-03-11 |          22 |        13 |          23 |        16 |      (null) |    (null) |

answered Jul 27, 2018 at 11:54

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

2 Comments

Joel Over a year ago

Thank you very much. Works perfectly!

D-Shih Over a year ago

@Joel Glad to help :)

Collectives™ on Stack Overflow

PostgreSQL multiple row as columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related