I got a pivot table:
ID Reference Key Value
-------------------------------------------------------------
01 001 date 03/04/2009
02 001 shift 1st
03 001 station 1 Mark
04 001 station 2 John
05 001 station 2 Macy
06 002 date 04/04/2009
07 002 shift 2nd
08 002 station 1 John
09 002 Station 1 Drey
10 002 Station 2 Macy
I currently got a view that generates the following table (used group_concat and then group by date field):
Date shift station 1 station 2
-----------------------------------------------------------------------
03/04/2009 1st Mark John, Macy
04/04/2009 2nd John, Drey Macy
And i would like to have:
Operator Date Shift Station
--------------------------------------------------------------------
Mark 03/04/2009 1st 1
Macy 03/04/2009 1st 2
Macy 04/04/2009 2nd 2
Drey 04/04/2009 2nd 1
John 03/04/2009 1st 2
John 04/04/2009 2nd 1
Tx a lot!
Please note that I'm not actually advocating this as a solution. Instead, you should normalize your design, particularly in regards the m:n relationship between shifts and operators, and also to reflect the different data types which are (or should be) involved...
DROP TABLE IF EXISTS eav_hell;
CREATE TABLE eav_hell
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,entity INT NOT NULL
,attribute VARCHAR(20) NOT NULL
,value VARCHAR(20) NOT NULL
);
INSERT INTO eav_hell (entity,attribute,value) VALUES
(1,'date','03/04/2009'),
(1,'shift','1st'),
(1,'station 1','Mark'),
(1,'station 2','John'),
(1,'station 2','Macy'),
(2,'date','04/04/2009'),
(2,'shift','2nd'),
(2,'station 1','John'),
(2,'Station 1','Drey'),
(2,'Station 2','Macy');
SELECT a.operator
, b.date
, b.shift
, b.station
FROM
( SELECT DISTINCT CASE WHEN attribute IN ('station 1','station 2') THEN value END operator FROM eav_hell ) a
JOIN
( SELECT entity
, 1 station
, MAX(CASE WHEN attribute = 'date' THEN value END) date
, MAX(CASE WHEN attribute = 'shift' THEN value END) shift
, GROUP_CONCAT(CASE WHEN attribute= 'station 1' THEN value END) names
FROM eav_hell
GROUP
BY entity
UNION
SELECT entity
, 2
, MAX(CASE WHEN attribute = 'date' THEN value END) date
, MAX(CASE WHEN attribute = 'shift' THEN value END) shift
, GROUP_CONCAT(CASE WHEN attribute= 'station 2' THEN value END)
FROM eav_hell
GROUP
BY entity
) b
ON FIND_IN_SET(a.operator,b.names) > 0;
+----------+------------+-------+---------+
| operator | date | shift | station |
+----------+------------+-------+---------+
| Mark | 03/04/2009 | 1st | 1 |
| John | 04/04/2009 | 2nd | 1 |
| John | 03/04/2009 | 1st | 2 |
| Macy | 03/04/2009 | 1st | 2 |
| Macy | 04/04/2009 | 2nd | 2 |
| Drey | 04/04/2009 | 2nd | 1 |
+----------+------------+-------+---------+