如何转置MySQL行并重复列标题? [英] How to transpose MySQL rows and repeat column headers?
本文介绍了如何转置MySQL行并重复列标题?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个如下所示的mysql表:
I have a mysql table that looks like:
id group_id item_code item_label item_detail item_score
1 10 BLU123 Blue 123 Blah blah 123 3
2 10 BLU124 Blue 124 Blah blah 124 6
3 10 BLU125 Blue 125 Blah blah 125 2
是否存在任何将表输出为以下内容的sql语句:
Is there any sql statement that will output the table as:
group_id item_code1 item_label1 item_detail1 item_score1 item_code2 item_label2 item_detail2 item_score2 item_code3 item_label3 item_detail3 item_score3
10 BLU123 Blue 123 Blah blah 123 3 BLU124 Blue 124 Blah blah 124 6 BLU125 Blue 125 Blah blah 125 2
谢谢!
推荐答案
如果这些确实是id
值,则可以执行以下操作:
You could do something like this if those really are the id
values:
select group_id,
max(case when id = 1 then item_code end) item_code1,
max(case when id = 1 then item_label end) item_label1,
max(case when id = 1 then item_detail end) iitem_detail1,
max(case when id = 1 then item_score end) item_score1,
max(case when id = 2 then item_code end) item_code2,
max(case when id = 2 then item_label end) item_label2,
max(case when id = 2 then item_detail end) iitem_detail2,
max(case when id = 2 then item_score end) item_score2,
max(case when id = 3 then item_code end) item_code3,
max(case when id = 3 then item_label end) item_label3,
max(case when id = 3 then item_detail end) iitem_detail3,
max(case when id = 3 then item_score end) item_score3
from yourtable
group by group_id
请参见带演示的SQL提琴
结果:
| GROUP_ID | ITEM_CODE1 | ITEM_LABEL1 | IITEM_DETAIL1 | ITEM_SCORE1 | ITEM_CODE2 | ITEM_LABEL2 | IITEM_DETAIL2 | ITEM_SCORE2 | ITEM_CODE3 | ITEM_LABEL3 | IITEM_DETAIL3 | ITEM_SCORE3 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 10 | BLU123 | Blue 123 | Blah blah 123 | 3 | BLU124 | Blue 124 | Blah blah 124 | 6 | BLU125 | Blue 125 | Blah blah 125 | 2 |
如果您不能依赖表中的id
,则可以在要返回的记录中添加行号:
If you cannot rely on the id
in the table, then you could add a row number to the records you are returning:
select group_id,
max(case when rownum = 1 then item_code end) item_code1,
max(case when rownum = 1 then item_label end) item_label1,
max(case when rownum = 1 then item_detail end) iitem_detail1,
max(case when rownum = 1 then item_score end) item_score1,
max(case when rownum = 2 then item_code end) item_code2,
max(case when rownum = 2 then item_label end) item_label2,
max(case when rownum = 2 then item_detail end) iitem_detail2,
max(case when rownum = 2 then item_score end) item_score2,
max(case when rownum = 3 then item_code end) item_code3,
max(case when rownum = 3 then item_label end) item_label3,
max(case when rownum = 3 then item_detail end) iitem_detail3,
max(case when rownum = 3 then item_score end) item_score3
from
(
select group_id, item_code, item_detail,
item_label, item_score,
@rn:=@rn+1 rownum
from yourtable, (SELECT @rn:=0) r
where group_id = 10
order by id
) src
group by group_id
请参见带有演示的SQL提琴
这篇关于如何转置MySQL行并重复列标题?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文