CODEWITHSUNDEEP
mysqlpivot
Lord of The Rings
Lord of The Rings

Reputation: 63

MySQL pivoting not working as expected

Schema:

DROP TABLE IF EXISTS `questions_tags`;
CREATE TABLE `questions_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `questions_tags` VALUES ('1', '1', '1');
INSERT INTO `questions_tags` VALUES ('2', '2', '1');
INSERT INTO `questions_tags` VALUES ('3', '3', '1');
INSERT INTO `questions_tags` VALUES ('4', '4', '1');
INSERT INTO `questions_tags` VALUES ('5', '5', '1');
INSERT INTO `questions_tags` VALUES ('6', '2', '2');

Data: 

id  tag_id  question_id
1     1         1
2     2         1
3     3         1
4     4         1
5     5         1
6     2         2

What I've tried:

SELECT 
question_id,
CASE WHEN tag_id = 1 THEN 'TAG1' END AS FirstTag,
CASE WHEN tag_id = 2 THEN 'TAG2' END AS SecondTag,
CASE WHEN tag_id = 3 THEN 'TAG3' END AS ThirdTag,
CASE WHEN tag_id = 4 THEN 'TAG4' END AS FourthTag,
CASE WHEN tag_id = 5 THEN 'TAG5' END AS FifthTag
FROM questions_tags
GROUP BY question_id;

Current Output:

enter image description here

Expected Output:

enter image description here

Is there something that I misjudged about Pivoting? Any help is appreciated.

Upvotes: 2

Views: 55

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use aggregation on the case expressions.

SELECT 
question_id,
max(CASE WHEN tag_id = 1 THEN 'TAG1' END) AS FirstTag,
max(CASE WHEN tag_id = 2 THEN 'TAG2' END) AS SecondTag,
max(CASE WHEN tag_id = 3 THEN 'TAG3' END) AS ThirdTag,
max(CASE WHEN tag_id = 4 THEN 'TAG4' END) AS FourthTag,
max(CASE WHEN tag_id = 5 THEN 'TAG5' END) AS FifthTag
FROM questions_tags
GROUP BY question_id;

Upvotes: 2

Related Questions