数据库行列转换
Tags: /MySQL/
Date Created:
如果有数据类似下面：
其中状态是每个id和sequenceNumber相同的时候的同一个事件的数据，如何转成行记录呢？
例如下面的方式：
代码：
http://stackoverflow.com/questions/23757493/how-do-i-convert-columns-into-rows-for-each-status
转换成下面的模式：
代码：
其中状态是每个id和sequenceNumber相同的时候的同一个事件的数据，如何转成行记录呢？
例如下面的方式：
代码：
SELECT T1.*, T2.`sent time`
FROM
(
SELECT pid, imsi, imei, `id`, sequenceNumber, sequenceSize, number, timeoffset AS "sending time"
FROM base_sms
WHERE `status` = "SENDING"
) T1
LEFT JOIN (
SELECT pid, imsi, imei, `id`, sequenceNumber, timeoffset AS "sent time"
FROM base_sms
WHERE `status` = "SENT"
) T2
ON T1.pid = T2.pid AND T1.`imsi` = T2.imsi AND T1.imei = T2.imei AND T1.`sequenceNumber` = T2.sequenceNumber AND T1.`id` = T2.`id`http://stackoverflow.com/questions/23757493/how-do-i-convert-columns-into-rows-for-each-status
转换成下面的模式：
代码：
SELECT Name, Action, Action_Date FROM (
SELECT Name, 'Joined' as Action, JOIN_DT as ACTION_DATE FROM TableA
UNION ALL
SELECT Name, 'Started', START_DT FROM TableA
UNION ALL
SELECT Name, 'Ended', END_DT FROM TableA)
ORDER BY Name;