Original Table A sid pos uuid acc 1 1 k1 9.1 2 1 k2 10.5 3 1 k3 8.3 4 2 k2 10.4 5 2 k1 7.2 6 2 k3 10.1 7 2 k3 6.2 8 2 k1 10.3 9 2 k2 9.4 into pos acc1(k1) acc2(k2) acc3(k3) 1 9.1 10.5 8.3 2 7.2 10.4 10.1 2 10.3 9.4 6.2 I've been googling for a week, I couldn't find solution... I really appreciate any help you can provide. MySQL please!
Asked
Active
Viewed 29 times
1
Kihyeon Kwon
- 13
- 4
-
Can you explain more clearly what you're trying to do? – Robbie Nov 04 '16 at 05:52
1 Answers
0
Use a pivot query:
SELECT pos,
MAX(CASE WHEN uuid = 'k1' THEN acc END) AS `acc1(k1)`,
MAX(CASE WHEN uuid = 'k2' THEN acc END) AS `acc2(k2)`,
MAX(CASE WHEN uuid = 'k3' THEN acc END) AS `acc3(k3)`
FROM yourTable
GROUP BY pos, (sid-1) DIV 3
ORDER BY pos
Demo here:
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
-
I've checked the most of the answer of http://stackoverflow.com/. – Kihyeon Kwon Nov 04 '16 at 05:39
-
-
@KihyeonKwon I just gave you an edit which should work, at least for your exact data set. – Tim Biegeleisen Nov 04 '16 at 06:05
-
Thank for your answer and espacially using the SQLFiddle. I've checked your solution. – Kihyeon Kwon Nov 04 '16 at 06:57