使用 MySQL 将垂直表转换为水平表 [英] Transform vertical table to horizontal with MySQL

问题描述

我有一个包含信息的 TableA，如下所示:

I have a TableA with information, like so:

TIME           DATA    VALUE 
-------------  ------  ------  
120520142546    Title   Mr  
120520142546    Name    Smith   
120520142546    Smoke   Yes
180303140429    Title   Ms
180303140429    Name    Lea
180303140429    Smoke   No

我正在尝试获取一个 TableB(已创建，只想插入值)，其中具有相同时间值的数据显示在同一行中，就像这样(并且还将是"转换为 1和否"为 0):

I'm trying to get a TableB (which is already created, just want to insert the value) where data with the same time value are displayed in the same row, like so (and also tranform the 'Yes' by 1 and 'No' by 0) :

ID      Title  Name    Smoke
---     -----  -----   -----
1       Mr     Smith    1
2       Ms     Lea      0

我有点理解它可以用 PIVOT 的东西来做，但我找不到一个我能理解的简单教程.

I kind of understand that it can be do with the PIVOT thing but i couldn't find an easy tutorial that I would understand.

推荐答案

试试这个:

CREATE TABLE TableB (
    Id int NOT NULL AUTO_INCREMENT,
    Title varchar(255) NOT NULL,
    Name varchar(255) NOT NULL,
    Smoke tinyint(255) NOT NULL,
    PRIMARY KEY (Id)
);

INSERT INTO TableB (Title, Name, Smoke)
SELECT t1.value AS title, t2.value AS name, (t3.value = 'Yes') AS smoke
FROM TableA t1
JOIN TableA t2 ON t1.time = t2.time
JOIN TableA t3 ON t1.time = t3.time
WHERE t1.data = 'Title' AND t2.data = 'Name' AND t3.data = 'Smoke';

这假设所有三个属性对于每个时间戳始终可用，并且任何时间戳都不存在重复条目.

This assumes all three attributes will always be available for each timestamp, and no duplicate entries exist for any timestamp.

这篇关于使用 MySQL 将垂直表转换为水平表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！