我有一个像这样的MySQL表:

我想把它从长格式转换成宽格式,就像这样

抱歉的。我是新来的,不知道怎么贴桌子
发布于 2014-03-28 13:50:58
试试这个:
insert into goodTable
select
bt.id,
(select bt1.value from badTable bt1 where bt1.info = 'firstname' and bt1.id = bt.id),
(select bt1.value from badTable bt1 where bt1.info = 'lastname' and bt1.id = bt.id),
(select bt1.value from badTable bt1 where bt1.info = 'phone' and bt1.id = bt.id)
from
badTable bt
group by id ;这里的工作小提琴:http://sqlfiddle.com/#!2/45f29e/2
发布于 2014-03-28 13:39:00
你需要这样的东西:
select id,
max(case when info='firstname' then value else null end),
max(case when info='lastname' then value else null end),
...
from table
group by id;发布于 2014-03-28 13:50:52
试试这个:
SELECT P.`info`,
(CASE
WHEN P.`action`='fname' AND P.`id` = '1'OR'2'
THEN P.`id`
ELSE NULL
END)AS 'firstname',
(CASE
WHEN P.`action`='lname' AND P.`id` = '2' OR'2'
THEN P.`id`
ELSE NULL
END) AS 'lastname',
(CASE
WHEN P.`action`='phone' AND P.`id` = '1'OR'2'
THEN P.`id`
ELSE NULL
END) AS 'phone'
FROM tablename P
GROUP BY P.`info`;https://stackoverflow.com/questions/22714009
复制相似问题