CODEWITHSUNDEEP
sqlamazon-redshift
Taha C
Taha C

Reputation: 23

SQL getting row data into columns (long to wide format)

I have a SQL table with some survey results with columns for the ID, Question, and Result.

Since the survey has, say, 5 questions, when I query this normally, I get 5 rows for each ID as shown.

How can I get all related data on a single row for each audit ID?

(This is of course dummy data)


ID  Question        Result
----------------------------
100 Are you male?   Yes
100 Hair Color      Brown
100 Eye Color       Brown
101 Are you male?   Yes
101 Hair Color      Blonde
101 Eye Color       Hazel
102 Are you male?   No
102 Hair Color      Black
102 Eye Color       Green
103 Are you male?   No
103 Hair Color      Black
103 Eye Color       Blue
104 Are you male?   Yes
104 Hair Color      Blonde
104 Eye Color       Brown
105 Are you male?   Yes
105 Hair Color      Brown
105 Eye Color       Brown

I'd like the end result formatted like so:


ID  Are you male?   Hair Color  Eye Color
-----------------------------------------
100 Yes             Brown       Brown
101 Yes             Blonde      Hazel
102 No              Black       Green
103 No              Black       Blue
104 Yes             Blonde      Brown
105 Yes             Brown       Brown

Upvotes: 2

Views: 369

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can use conditional aggregation:

select id,
       max(case when question = 'Are you male?' then result end) as are_you_male,
       max(case when question = 'Hair Color' then result end) as hair_color,
       max(case when question = 'Eye Color' then result end) as eye_color
from t
group by id;

Upvotes: 2

Related Questions