mysql列转列语句,SQL：在MySQL中将行转换为列（SELECT语句）

I got table orders and order_comments. Each order can have from 0 to n comments. I would like to get list of all orders with their comments in a sepcific order.

Table orders:

order_id | order_nr

1 | 5252

4 | 6783

5 | 6785

Table order_comments

id_order_comments | order_fk | created_at | email | content

1 | 4 | 2015-01-12 | jack | some text here

2 | 5 | 2015-01-13 | marta | some text here

3 | 5 | 2015-01-14 | beata | some text here

4 | 4 | 2015-01-16 | julia | some text here

As a result, I would like to get 1 row for each order. Comments should be shown in separate columns, starting from the oldest comment. So desired output in this case is:

order_id | 1_comment_created_at | 1_comment_author | 1_comment_content | 2_comment_created_at | 2_comment_author | 2_comment_content

1 | NULL | NULL | NULL | NULL | NULL | NULL

4 | 2015-01-12 | jack | some text here | 2015-01-16 | Julia | some text here

5 | 2015-01-13 | marta | some text here | 2015-01-14 | beata | some text here

I found this: MySQL - Rows to Columns - but I cannot use 'create view'.

I found this: http://dev.mysql.com/doc/refman/5.5/en/while.html - but I cannot create procedure in this db.

What I got:

SELECT @c := (SELECT count(*) FROM order_comments GROUP BY order_fk ORDER BY count(*) DESC LIMIT 1);

SET @rank=0;

SET @test=0;

SELECT

CASE WHEN @test < @c AND temp.comment_id = @test THEN temp.created_at END AS created,

CASE WHEN @test < @c AND temp.comment_id = @test THEN temp.author END AS author,

CASE WHEN @test < @c AND temp.comment_id = @test THEN temp.content END AS content

/*But I cannot set @test as +1. And I cannot name column with variable - like CONCAT(@test, '_created')*/

FROM (

SELECT @rank := @rank +1 AS comment_id, created_at, author, content

FROM order_comments

WHERE order_fk = 4

ORDER BY created_at

) AS temp

Problem: I would like to search more than 1 order. I should get orders with no comments too.

What can I do?

解决方案

You can use variables for this type of pivot, but the query is a bit more complicated, because you need to enumerate the values for each order:

SELECT o.order_id,

MAX(case when rank = 1 then created_at end) as created_at_1,

MAX(case when rank = 1 then email end) as email_1,

MAX(case when rank = 1 then content end) as content_1,

MAX(case when rank = 2 then created_at end) as created_at_2,

MAX(case when rank = 2 then email end) as email_2,

MAX(case when rank = 2 then content end) as content_2,

FROM orders o LEFT JOIN

(SELECT oc.*,

(@rn := if(@o = order_fk, @rn + 1,

if(@o := order_fk, 1, 1)

)

) as rank

FROM order_comments oc CROSS JOIN

(SELECT @rn := 0, @o := 0) vars

ORDER BY order_fk, created_at

) oc

ON o.order_id = oc.order_fk

GROUP BY o.order_id;