How to display column attribute values as column in mysql

Question

I have a table with the following entries:

+------------+-----------+----------+
| screenId   | userInput | numInput |
+------------+-----------+----------+
| 13_1_2_1   | 2         |        9 |
| 13_1_2_2   | 2         |        9 |
| 13_1_2_2   | 3         |        2 |
| 13_1_2_2   | 9         |        2 |
| 13_1_2_2_2 | 3         |        3 |
| 13_1_2_2_2 | 5         |        2 |
| 13_2_2_2   | 4         |        4 |
| 13_2_2_2   | 5         |        4 |
| 13_2_2_2   | 7         |        2 |
+------------+-----------+----------+

I need to frame a query which gives the output as:

+------------+---+---+---+---+---+---+---+---+---+---+
| screenId   | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
+------------+---+---+---+---+---+---+---+---+---+---+
| 13_1_2_1   | 0 | 0 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 13_1_2_2   | 0 | 0 | 9 | 2 | 0 | 0 | 0 | 0 | 0 | 2 |
| 13_1_2_2_2 | 0 | 0 | 0 | 3 | 0 | 2 | 0 | 0 | 0 | 0 |
| 13_2_2_2   | 0 | 0 | 0 | 0 | 4 | 4 | 0 | 2 | 0 | 0 |
+------------+---+---+---+---+---+---+---+---+---+---+

Here 0-9 column values are the userInput values and the values against them are the numInput values. for example first row means the numInput for the value userInput '2' and screenID '13_1_2_1' is '9'.

I am unable to frame a proper query for this problem. Please help.

It's much better/easier/more flexible/scalable to handle issues of data display at the presentation layer/application-levl code (e.g. a bit of PHP) assuming you have that. — Strawberry
– Strawberry, Commented Sep 19, 2014 at 10:01

Strawberry · Accepted Answer · 2014-09-19 11:59:29Z

1

Incidentally, the standard solution (when a presentation layer is unavailable for some strange reason) is as follows. Note however that repreated OUTER JOINs may in fact be fractionally faster!

SELECT screenid
     , MAX(CASE WHEN userinput = 0 THEN numinput ELSE 0 END) '0'
     , MAX(CASE WHEN userinput = 1 THEN numinput ELSE 0 END) '1'
     , MAX(CASE WHEN userinput = 2 THEN numinput ELSE 0 END) '2'
     , MAX(CASE WHEN userinput = 3 THEN numinput ELSE 0 END) '3'
     , MAX(CASE WHEN userinput = 4 THEN numinput ELSE 0 END) '4'
     , MAX(CASE WHEN userinput = 5 THEN numinput ELSE 0 END) '5'
     , MAX(CASE WHEN userinput = 6 THEN numinput ELSE 0 END) '6'
     , MAX(CASE WHEN userinput = 7 THEN numinput ELSE 0 END) '7'
     , MAX(CASE WHEN userinput = 8 THEN numinput ELSE 0 END) '8'
     , MAX(CASE WHEN userinput = 9 THEN numinput ELSE 0 END) '9'
  FROM my_table
 GROUP
    BY screenid;

Oh, and calling columns '0', '1', '2', '3', etc. is opening up a world of pain

answered Sep 19, 2014 at 11:59

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vanellope Over a year ago

Sir, Thank you for your help. I need to generate a .csv file from the above mentioned table using bash script. Using the above output it can be generated easily by using awk command. Is there any easy and efficient way to generate a .csv file. Please suggest. the .csv file data has to be of the form: 13_2_2_2,0,0,0,0,4,4,0,2,0,0

Strawberry Over a year ago

If you have a new question, ask a new question!

asbb · Accepted Answer · 2014-09-19 10:32:34Z

It's very, very bad…

SELECT
    p.screenId,
    p0.numInput `0`,
    p1.numInput `1`,
    p2.numInput `2`,
    p3.numInput `3`,
    p4.numInput `4`,
    p5.numInput `5`,
    p6.numInput `6`,
    p7.numInput `7`,
    p8.numInput `8`,
    p9.numInput `9`,
FROM
    screens p
    LEFT JOIN screens p0 ON p0.screenId=p.screenId AND p0.userInput=0
    LEFT JOIN screens p1 ON p1.screenId=p.screenId AND p1.userInput=1
    LEFT JOIN screens p2 ON p2.screenId=p.screenId AND p2.userInput=2
    LEFT JOIN screens p2 ON p3.screenId=p.screenId AND p3.userInput=3
    LEFT JOIN screens p2 ON p4.screenId=p.screenId AND p4.userInput=4
    LEFT JOIN screens p2 ON p5.screenId=p.screenId AND p5.userInput=5
    LEFT JOIN screens p2 ON p6.screenId=p.screenId AND p6.userInput=6
    LEFT JOIN screens p2 ON p7.screenId=p.screenId AND p7.userInput=7
    LEFT JOIN screens p2 ON p8.screenId=p.screenId AND p8.userInput=8
    LEFT JOIN screens p2 ON p9.screenId=p.screenId AND p9.userInput=9
GROUP BY
    p.screenId

vvahans · Accepted Answer · 2014-09-19 10:44:51Z

Another very bad solution :)

SELECT DISTINCT screenId, 
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '0' ) as '0',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '1' ) as '1',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '2' ) as '2',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '3' ) as '3',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '4' ) as '4',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '5' ) as '5',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '6' ) as '6',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '7' ) as '7',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '8' ) as '8',
(select IF(sum(numInput) IS NOT NULL, sum(numInput), 0) from quest as q where q.screenId = quest.screenId and q.userInput = '9' ) as '9'
from quest;

I quess there is a better solution for your "pivot" like result table using group operators. I will update when I found.

Collectives™ on Stack Overflow

How to display column attribute values as column in mysql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related