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, Jul 27 '18 at 11:43

score 3 · Accepted Answer · answered Jul 27 '18 at 11:54

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

Thank you very much. Works perfectly! – Joel Jul 27 '18 at 16:52 — Joel, Jul 27 '18 at 16:52

PostgreSQL multiple row as columns

1 Answers1