-1

This question already has an answer here:

I am trying to pivot data. The column names have to be dynamic based on the row values for subject_id column.

Table

Sid  marks subject_id
101   20    3
101   25    2
101   60    1
104   22    3
104   54    2
104   45    1

I need the data in this format:

Sid   3   2   1 
101  20  25  60 
104  22  54  45

marked as duplicate by Paul White Aug 18 '15 at 0:38

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

Data:

mysql> select * from sid;
+-----+-------+------------+
| Sid | marks | subject_id |
+-----+-------+------------+
| 101 |    20 |          3 |
| 101 |    25 |          2 |
| 101 |    60 |          1 |
| 104 |    22 |          3 |
| 104 |    45 |          1 |
| 104 |    54 |          2 |
+-----+-------+------------+
6 rows in set (0.00 sec)

mysql> 

Method 1:

Using this information I've made an example with your data:

Query 1:

SELECT
    Sid,
    SUM(marks*(1-ABS(SIGN(subject_id-3)))) as `3`,
    SUM(marks*(1-ABS(SIGN(subject_id-2)))) as `2`,
    SUM(marks*(1-ABS(SIGN(subject_id-1)))) as `1`
FROM sid
GROUP BY Sid;

Test:

mysql>     SELECT
    ->     Sid,
    ->     SUM(marks*(1-ABS(SIGN(subject_id-3)))) as `3`,
    ->     SUM(marks*(1-ABS(SIGN(subject_id-2)))) as `2`,
    ->     SUM(marks*(1-ABS(SIGN(subject_id-1)))) as `1`
    ->     FROM sid
    ->     GROUP BY Sid;
+-----+------+------+------+
| Sid | 3    | 2    | 1    |
+-----+------+------+------+
| 101 |   20 |   25 |   60 |
| 104 |   22 |   54 |   45 |
+-----+------+------+------+
2 rows in set (0.00 sec)

mysql> 

As the page says:

The simple secret, and it's also why they work in almost all databases, is the following functions:

sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively

abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0 1-abs(

sign( x) ) complement of the above, since this returns 1 only if x = 0

Method 2:

Query 2:

SELECT 
    s.Sid,
    s3.marks AS `3`,
    s2.marks AS `2`,
    s1.marks AS `1`
FROM sid AS s
JOIN sid AS s3 ON (s3.subject_id=3 AND s.Sid=s3.Sid)
JOIN sid AS s2 ON (s2.subject_id=2 AND s.Sid=s2.Sid)
JOIN sid AS s1 ON (s1.subject_id=1 AND s.Sid=s1.Sid)
GROUP BY s.Sid,s3.subject_id;

Test:

mysql> SELECT 
    -> s.Sid,
    -> s3.marks AS `3`,
    ->     s2.marks AS `2`,
    ->     s1.marks AS `1`
    -> FROM sid AS s
    -> JOIN sid AS s3 ON (s3.subject_id=3 AND s.Sid=s3.Sid)
    -> JOIN sid AS s2 ON (s2.subject_id=2 AND s.Sid=s2.Sid)
    -> JOIN sid AS s1 ON (s1.subject_id=1 AND s.Sid=s1.Sid)
    -> GROUP BY s.Sid,s3.subject_id;
+-----+----+----+----+
| Sid | 3  | 2  | 1  |
+-----+----+----+----+
| 101 | 20 | 25 | 60 |
| 104 | 22 | 54 | 45 |
+-----+----+----+----+
2 rows in set (0.00 sec)

mysql> 

Not the answer you're looking for? Browse other questions tagged or ask your own question.