0

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

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

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.

Antt
  • 159
  • 2
  • 13
  • possible duplicate of [MySQL pivot table query with dynamic columns](http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) – Sadikhasan Jul 08 '15 at 08:21

1 Answers1

2

Try this:

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.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • Thanks, it's a little bit basic since i have many data and not just three but that will do the work just fine. – Antt Jul 08 '15 at 08:47
  • Actually, with the number of value I have (13) this method doesn't work. It run for an undifined period of time an make chrome crash with phpMyAdmin. – Antt Jul 08 '15 at 14:30
  • try indexing the time and data columns – reaanb Jul 08 '15 at 14:33
  • Unfortunally I have no control over that table, maybe should I clone it ? – Antt Jul 08 '15 at 14:40