Convert SQL rows to columns

Question

This is my table:

id | num | comment
---+-----+--------
 3 |  10 | hello
 3 |  20 | pls
 3 |  30 | respond
 7 |  10 | leet
 7 |  20 | hax
 7 |  30 | zor

How can I query it out in this manner:

id | first | second | third
---+-------+--------+--------
 3 | hello |  pls   | respond
 7 | leet  |  hax   | zor

This has been asked and answered before. http://stackoverflow.com/questions/3120835/how-to-pivot-rows-into-columns-custom-pivoting http://stackoverflow.com/questions/3601681/convert-sql-row-into-columns — Robert Dean, Jul 29 '14 at 23:12
Does the num column always ascend by 10 like that, ie. 10, 20, 30, 40 etc. ? — Brian DeMilia, Jul 29 '14 at 23:16
Technique you are looking for is called pivot...search for it on stack exchange and you'll find hundreds of posts asking for the same thing. — Twelfth, Jul 29 '14 at 23:24

score 2 · Accepted Answer · edited May 23 '17 at 12:11

In the event that the num column does not reliably always start at 10 and ascend by 10 you can use the following to establish a row number that restarts at each change in ID, that way you can use the rownumbers in conjunction with conditional aggregation to show each comment. The following would do so for up to 10 comments per ID, and the NUM column does not have to be 10/20/30/40/50/60/70/80/90 (it could be anything).

If the NUM column reliably starts at 10 and ascends by 10, this question has been asked and answered: How to pivot rows into columns (custom pivoting)

select id,
       max(case when row_number = 1 then comment else null end) as c01,
       max(case when row_number = 2 then comment else null end) as c02,
       max(case when row_number = 3 then comment else null end) as c03,
       max(case when row_number = 4 then comment else null end) as c04,
       max(case when row_number = 5 then comment else null end) as c05,
       max(case when row_number = 6 then comment else null end) as c06,
       max(case when row_number = 7 then comment else null end) as c07,
       max(case when row_number = 8 then comment else null end) as c08,
       max(case when row_number = 9 then comment else null end) as c09,
       max(case when row_number = 10 then comment else null end) as c10
from(
select @row_number := case when @prev_val = id then @row_number+1 else 1 end as row_number,
       id,
       comment,
       @prev_val:=id as prev_val
from tbl, (select @row_number:=0,@prev_val:='') x
order by id, num) x
group by id
order by id

Convert SQL rows to columns

1 Answers1