Mixing
Convert row wise data into column wise data in mysql
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
I have a data of subject wise students marks in rows. I want it to be converted into column wise per each student.
I have gone through Stack overflow and saw few solutions and tried those, but none of those are helping or I'm not executing those properly.
Input:
UID SUBJECT MARKS
16001 Chemistry 45
16001 CS 52
16001 English 28
16002 Chemistry 25
16002 CS 25
16002 English 50
16011 Biology 25
16011 Chemistry 30
16011 English 40
16011 Physics 50
Expected Output
UID Maths Physics Chemistry Biology CS English
16001 - - 45 - 52 28
16002 - - 25 - 25 50
16011 - 50 30 25 - 40
Query Written
select uid,
(case when subject='Maths' then Marks else '-' end) Maths,
(case when subject='Physics' then Marks else '-' end) Physics,
(case when subject='Chemistry' then Marks else '-' end) Chemistry,
(case when subject='Biology' then Marks else '-' end) Biology,
(case when subject='CS' then Marks else '-' end) CS,
(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
Output I'm getting
UID Maths Physics Chemistry Biology CS English
16001 - - 45 - - -
16002 - - 25 - - -
16003 - - 42 - - -
16011 - - - 25 - -
Which means I'm getting only the first entry of each student. What is the mistake in this code. Any help is appreciated. Thank-you.
mysql
asked Jan 3 at 12:00
Sai PavanSai Pavan
134
add a comment |
1
I have a data of subject wise students marks in rows. I want it to be converted into column wise per each student.
I have gone through Stack overflow and saw few solutions and tried those, but none of those are helping or I'm not executing those properly.
Input:
UID SUBJECT MARKS
16001 Chemistry 45
16001 CS 52
16001 English 28
16002 Chemistry 25
16002 CS 25
16002 English 50
16011 Biology 25
16011 Chemistry 30
16011 English 40
16011 Physics 50
Expected Output
UID Maths Physics Chemistry Biology CS English
16001 - - 45 - 52 28
16002 - - 25 - 25 50
16011 - 50 30 25 - 40
Query Written
select uid,
(case when subject='Maths' then Marks else '-' end) Maths,
(case when subject='Physics' then Marks else '-' end) Physics,
(case when subject='Chemistry' then Marks else '-' end) Chemistry,
(case when subject='Biology' then Marks else '-' end) Biology,
(case when subject='CS' then Marks else '-' end) CS,
(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
Output I'm getting
UID Maths Physics Chemistry Biology CS English
16001 - - 45 - - -
16002 - - 25 - - -
16003 - - 42 - - -
16011 - - - 25 - -
Which means I'm getting only the first entry of each student. What is the mistake in this code. Any help is appreciated. Thank-you.
mysql
asked Jan 3 at 12:00
Sai PavanSai Pavan
134
add a comment |
1
1
1
I have a data of subject wise students marks in rows. I want it to be converted into column wise per each student.
I have gone through Stack overflow and saw few solutions and tried those, but none of those are helping or I'm not executing those properly.
Input:
UID SUBJECT MARKS
16001 Chemistry 45
16001 CS 52
16001 English 28
16002 Chemistry 25
16002 CS 25
16002 English 50
16011 Biology 25
16011 Chemistry 30
16011 English 40
16011 Physics 50
Expected Output
UID Maths Physics Chemistry Biology CS English
16001 - - 45 - 52 28
16002 - - 25 - 25 50
16011 - 50 30 25 - 40
Query Written
select uid,
(case when subject='Maths' then Marks else '-' end) Maths,
(case when subject='Physics' then Marks else '-' end) Physics,
(case when subject='Chemistry' then Marks else '-' end) Chemistry,
(case when subject='Biology' then Marks else '-' end) Biology,
(case when subject='CS' then Marks else '-' end) CS,
(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
Output I'm getting
UID Maths Physics Chemistry Biology CS English
16001 - - 45 - - -
16002 - - 25 - - -
16003 - - 42 - - -
16011 - - - 25 - -
Which means I'm getting only the first entry of each student. What is the mistake in this code. Any help is appreciated. Thank-you.
mysql
asked Jan 3 at 12:00
Sai PavanSai Pavan
134
I have a data of subject wise students marks in rows. I want it to be converted into column wise per each student.
I have gone through Stack overflow and saw few solutions and tried those, but none of those are helping or I'm not executing those properly.
Input:
UID SUBJECT MARKS
16001 Chemistry 45
16001 CS 52
16001 English 28
16002 Chemistry 25
16002 CS 25
16002 English 50
16011 Biology 25
16011 Chemistry 30
16011 English 40
16011 Physics 50
Expected Output
UID Maths Physics Chemistry Biology CS English
16001 - - 45 - 52 28
16002 - - 25 - 25 50
16011 - 50 30 25 - 40
Query Written
select uid,
(case when subject='Maths' then Marks else '-' end) Maths,
(case when subject='Physics' then Marks else '-' end) Physics,
(case when subject='Chemistry' then Marks else '-' end) Chemistry,
(case when subject='Biology' then Marks else '-' end) Biology,
(case when subject='CS' then Marks else '-' end) CS,
(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
Output I'm getting
UID Maths Physics Chemistry Biology CS English
16001 - - 45 - - -
16002 - - 25 - - -
16003 - - 42 - - -
16011 - - - 25 - -
Which means I'm getting only the first entry of each student. What is the mistake in this code. Any help is appreciated. Thank-you.
mysql
mysql
asked Jan 3 at 12:00
Sai PavanSai Pavan
134
asked Jan 3 at 12:00
Sai PavanSai Pavan
134
asked Jan 3 at 12:00
Sai PavanSai Pavan
134
asked Jan 3 at 12:00
Sai PavanSai Pavan
134
asked Jan 3 at 12:00
Sai PavanSai Pavan
134
134
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
You can try below - you've to use max/min aggregation here
select uid,
max(case when subject='Maths' then Marks else '-' end) Maths,
max(case when subject='Physics' then Marks else '-' end) Physics,
max(case when subject='Chemistry' then Marks else '-' end) Chemistry,
max(case when subject='Biology' then Marks else '-' end) Biology,
max(case when subject='CS' then Marks else '-' end) CS,
max(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
answered Jan 3 at 12:03
fa06fa06
19.2k21019
add a comment |
0
Keep it simple with multiple table joins to the same table. This should be well optimized by MySql.
Written on the fly, not tested but this should work.
SELECT
id.UID UID,
math.marks Maths,
phys.marks Pysics,
bio.marks Biology,
cs.marks CS,
engl.marks English
FROM (SELECT DISTINCT UID FROM subWiseMarks) id
LEFT JOIN subWiseMarks math
LEFT JOIN subWiseMarks phys USING(UID)
LEFT JOIN subWiseMarks chem USING(UID)
LEFT JOIN subWiseMarks bio USING(UID)
LEFT JOIN subWiseMarks cs USING(UID)
LEFT JOIN subWiseMarks engl USING(UID)
WHERE math.SUBJECT = 'Maths'
AND phys.SUBJECT = 'Physics'
AND phys.SUBJECT = 'Chemistry'
AND phys.SUBJECT = 'Biology'
AND phys.SUBJECT = 'CS'
AND phys.SUBJECT = 'English'
;
answered Jan 3 at 12:24
Quasimodo's cloneQuasimodo's clone
4,66221131
I have 4 more subjects, which I didn't post in question. It makes my query too lengthy. Thank-you for your response.
– Sai Pavan
Jan 3 at 12:35
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021896%2fconvert-row-wise-data-into-column-wise-data-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You can try below - you've to use max/min aggregation here
select uid,
max(case when subject='Maths' then Marks else '-' end) Maths,
max(case when subject='Physics' then Marks else '-' end) Physics,
max(case when subject='Chemistry' then Marks else '-' end) Chemistry,
max(case when subject='Biology' then Marks else '-' end) Biology,
max(case when subject='CS' then Marks else '-' end) CS,
max(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
answered Jan 3 at 12:03
fa06fa06
19.2k21019
add a comment |
0
You can try below - you've to use max/min aggregation here
select uid,
max(case when subject='Maths' then Marks else '-' end) Maths,
max(case when subject='Physics' then Marks else '-' end) Physics,
max(case when subject='Chemistry' then Marks else '-' end) Chemistry,
max(case when subject='Biology' then Marks else '-' end) Biology,
max(case when subject='CS' then Marks else '-' end) CS,
max(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
answered Jan 3 at 12:03
fa06fa06
19.2k21019
add a comment |
0
0
0
You can try below - you've to use max/min aggregation here
select uid,
max(case when subject='Maths' then Marks else '-' end) Maths,
max(case when subject='Physics' then Marks else '-' end) Physics,
max(case when subject='Chemistry' then Marks else '-' end) Chemistry,
max(case when subject='Biology' then Marks else '-' end) Biology,
max(case when subject='CS' then Marks else '-' end) CS,
max(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
answered Jan 3 at 12:03
fa06fa06
19.2k21019
You can try below - you've to use max/min aggregation here
select uid,
max(case when subject='Maths' then Marks else '-' end) Maths,
max(case when subject='Physics' then Marks else '-' end) Physics,
max(case when subject='Chemistry' then Marks else '-' end) Chemistry,
max(case when subject='Biology' then Marks else '-' end) Biology,
max(case when subject='CS' then Marks else '-' end) CS,
max(case when subject='English' then Marks else '-' end) English
from subWiseMarks
group by uid;
answered Jan 3 at 12:03
fa06fa06
19.2k21019
answered Jan 3 at 12:03
fa06fa06
19.2k21019
answered Jan 3 at 12:03
fa06fa06
19.2k21019
answered Jan 3 at 12:03
fa06fa06
19.2k21019
19.2k21019
add a comment |
add a comment |
0
Keep it simple with multiple table joins to the same table. This should be well optimized by MySql.
Written on the fly, not tested but this should work.
SELECT
id.UID UID,
math.marks Maths,
phys.marks Pysics,
bio.marks Biology,
cs.marks CS,
engl.marks English
FROM (SELECT DISTINCT UID FROM subWiseMarks) id
LEFT JOIN subWiseMarks math
LEFT JOIN subWiseMarks phys USING(UID)
LEFT JOIN subWiseMarks chem USING(UID)
LEFT JOIN subWiseMarks bio USING(UID)
LEFT JOIN subWiseMarks cs USING(UID)
LEFT JOIN subWiseMarks engl USING(UID)
WHERE math.SUBJECT = 'Maths'
AND phys.SUBJECT = 'Physics'
AND phys.SUBJECT = 'Chemistry'
AND phys.SUBJECT = 'Biology'
AND phys.SUBJECT = 'CS'
AND phys.SUBJECT = 'English'
;
answered Jan 3 at 12:24
Quasimodo's cloneQuasimodo's clone
4,66221131
I have 4 more subjects, which I didn't post in question. It makes my query too lengthy. Thank-you for your response.
– Sai Pavan
Jan 3 at 12:35
add a comment |
0
Keep it simple with multiple table joins to the same table. This should be well optimized by MySql.
Written on the fly, not tested but this should work.
SELECT
id.UID UID,
math.marks Maths,
phys.marks Pysics,
bio.marks Biology,
cs.marks CS,
engl.marks English
FROM (SELECT DISTINCT UID FROM subWiseMarks) id
LEFT JOIN subWiseMarks math
LEFT JOIN subWiseMarks phys USING(UID)
LEFT JOIN subWiseMarks chem USING(UID)
LEFT JOIN subWiseMarks bio USING(UID)
LEFT JOIN subWiseMarks cs USING(UID)
LEFT JOIN subWiseMarks engl USING(UID)
WHERE math.SUBJECT = 'Maths'
AND phys.SUBJECT = 'Physics'
AND phys.SUBJECT = 'Chemistry'
AND phys.SUBJECT = 'Biology'
AND phys.SUBJECT = 'CS'
AND phys.SUBJECT = 'English'
;
answered Jan 3 at 12:24
Quasimodo's cloneQuasimodo's clone
4,66221131
I have 4 more subjects, which I didn't post in question. It makes my query too lengthy. Thank-you for your response.
– Sai Pavan
Jan 3 at 12:35
add a comment |
0
0
0
Keep it simple with multiple table joins to the same table. This should be well optimized by MySql.
Written on the fly, not tested but this should work.
SELECT
id.UID UID,
math.marks Maths,
phys.marks Pysics,
bio.marks Biology,
cs.marks CS,
engl.marks English
FROM (SELECT DISTINCT UID FROM subWiseMarks) id
LEFT JOIN subWiseMarks math
LEFT JOIN subWiseMarks phys USING(UID)
LEFT JOIN subWiseMarks chem USING(UID)
LEFT JOIN subWiseMarks bio USING(UID)
LEFT JOIN subWiseMarks cs USING(UID)
LEFT JOIN subWiseMarks engl USING(UID)
WHERE math.SUBJECT = 'Maths'
AND phys.SUBJECT = 'Physics'
AND phys.SUBJECT = 'Chemistry'
AND phys.SUBJECT = 'Biology'
AND phys.SUBJECT = 'CS'
AND phys.SUBJECT = 'English'
;
answered Jan 3 at 12:24
Quasimodo's cloneQuasimodo's clone
4,66221131
Keep it simple with multiple table joins to the same table. This should be well optimized by MySql.
Written on the fly, not tested but this should work.
SELECT
id.UID UID,
math.marks Maths,
phys.marks Pysics,
bio.marks Biology,
cs.marks CS,
engl.marks English
FROM (SELECT DISTINCT UID FROM subWiseMarks) id
LEFT JOIN subWiseMarks math
LEFT JOIN subWiseMarks phys USING(UID)
LEFT JOIN subWiseMarks chem USING(UID)
LEFT JOIN subWiseMarks bio USING(UID)
LEFT JOIN subWiseMarks cs USING(UID)
LEFT JOIN subWiseMarks engl USING(UID)
WHERE math.SUBJECT = 'Maths'
AND phys.SUBJECT = 'Physics'
AND phys.SUBJECT = 'Chemistry'
AND phys.SUBJECT = 'Biology'
AND phys.SUBJECT = 'CS'
AND phys.SUBJECT = 'English'
;
answered Jan 3 at 12:24
Quasimodo's cloneQuasimodo's clone
4,66221131
edited Jan 3 at 12:37
answered Jan 3 at 12:24
Quasimodo's cloneQuasimodo's clone
4,66221131
answered Jan 3 at 12:24
Quasimodo's cloneQuasimodo's clone
4,66221131
answered Jan 3 at 12:24
Quasimodo's cloneQuasimodo's clone
4,66221131
4,66221131
I have 4 more subjects, which I didn't post in question. It makes my query too lengthy. Thank-you for your response.
– Sai Pavan
Jan 3 at 12:35
add a comment |
I have 4 more subjects, which I didn't post in question. It makes my query too lengthy. Thank-you for your response.
– Sai Pavan
Jan 3 at 12:35
I have 4 more subjects, which I didn't post in question. It makes my query too lengthy. Thank-you for your response.
– Sai Pavan
Jan 3 at 12:35
I have 4 more subjects, which I didn't post in question. It makes my query too lengthy. Thank-you for your response.
– Sai Pavan
Jan 3 at 12:35
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54021896%2fconvert-row-wise-data-into-column-wise-data-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
