Transpose part of SQL results to consolidate records
I’m working on a project and it’s become a bit more complicated than I’m able to deal with. I have a piece of SQL that inner joins to several tables. One of these tables returns communication means for each individual (telephone number, radio call sign, pager, for example).
Once I’ve implemented this code, the SQL (quite rightly) returns multiple records for each individual, depending on which comms fields they’ve got filled out. So a search for myself would return:
Name | COMM_VALUE | COMM_RID
Nick | 07123 456789 | 5
Nick | 0965 | 6
Nick | 328493 | 7
Where as, I need:
Name | Mobile | Radio | Pager
Nick | 07123 456789 | 0965 | 328493
Bob | etc etc etc
This is part of much larger table so my question is how do I add these columns to my existing SQL, I looked in to nested, subqueries, and unions but it’s an age since I did SQL and even my basic progress has been testing my capability.
I envisage something like the following but not sure of the keywords I need to achieve this:
Select
PRP.FIRSTNAME + ‘ ‘ + PRP.SURNAME AS FULLNAME,
RDP.POSTNAME,
RRP.REMARKS,
*/ NEW COLUMNS */
(SELECT COMM_VALUE FROM PERREC_COMMS WHERE COMM_RID = ‘6’) AS RADIO
The above (edited) code does yield results, but not quite as I anticipated:
Name | RADIO
Nick | 0965
Nick | null
Nick | null
The above is then repeated for each person, with the other fields appearing correctly although duplicated on each row.
How can I consolidate these results to one row (exclude the ‘null’ results)?
mysql sql
asked Jan 19 at 9:14
TheMiddleTheMiddle
237
|
show 1 more comment
I’m working on a project and it’s become a bit more complicated than I’m able to deal with. I have a piece of SQL that inner joins to several tables. One of these tables returns communication means for each individual (telephone number, radio call sign, pager, for example).
Once I’ve implemented this code, the SQL (quite rightly) returns multiple records for each individual, depending on which comms fields they’ve got filled out. So a search for myself would return:
Name | COMM_VALUE | COMM_RID
Nick | 07123 456789 | 5
Nick | 0965 | 6
Nick | 328493 | 7
Where as, I need:
Name | Mobile | Radio | Pager
Nick | 07123 456789 | 0965 | 328493
Bob | etc etc etc
This is part of much larger table so my question is how do I add these columns to my existing SQL, I looked in to nested, subqueries, and unions but it’s an age since I did SQL and even my basic progress has been testing my capability.
I envisage something like the following but not sure of the keywords I need to achieve this:
Select
PRP.FIRSTNAME + ‘ ‘ + PRP.SURNAME AS FULLNAME,
RDP.POSTNAME,
RRP.REMARKS,
*/ NEW COLUMNS */
(SELECT COMM_VALUE FROM PERREC_COMMS WHERE COMM_RID = ‘6’) AS RADIO
The above (edited) code does yield results, but not quite as I anticipated:
Name | RADIO
Nick | 0965
Nick | null
Nick | null
The above is then repeated for each person, with the other fields appearing correctly although duplicated on each row.
How can I consolidate these results to one row (exclude the ‘null’ results)?
mysql sql
asked Jan 19 at 9:14
TheMiddleTheMiddle
237
There's no pcomm_rid here.
– Strawberry
Jan 19 at 9:43
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 19 at 9:43
Typo, sorry. Will check your link
– TheMiddle
Jan 19 at 9:46
There's no comm _rid here either
– Strawberry
Jan 19 at 9:47
There is a comm_rid field in PERREC_COMMS. PERREC_COMMS is inner joined later in the statement. I’ve not included the full statement as it’s too long to retype. (SO is banned at work ;-( )
– TheMiddle
Jan 19 at 9:49
|
show 1 more comment
I’m working on a project and it’s become a bit more complicated than I’m able to deal with. I have a piece of SQL that inner joins to several tables. One of these tables returns communication means for each individual (telephone number, radio call sign, pager, for example).
Once I’ve implemented this code, the SQL (quite rightly) returns multiple records for each individual, depending on which comms fields they’ve got filled out. So a search for myself would return:
Name | COMM_VALUE | COMM_RID
Nick | 07123 456789 | 5
Nick | 0965 | 6
Nick | 328493 | 7
Where as, I need:
Name | Mobile | Radio | Pager
Nick | 07123 456789 | 0965 | 328493
Bob | etc etc etc
This is part of much larger table so my question is how do I add these columns to my existing SQL, I looked in to nested, subqueries, and unions but it’s an age since I did SQL and even my basic progress has been testing my capability.
I envisage something like the following but not sure of the keywords I need to achieve this:
Select
PRP.FIRSTNAME + ‘ ‘ + PRP.SURNAME AS FULLNAME,
RDP.POSTNAME,
RRP.REMARKS,
*/ NEW COLUMNS */
(SELECT COMM_VALUE FROM PERREC_COMMS WHERE COMM_RID = ‘6’) AS RADIO
The above (edited) code does yield results, but not quite as I anticipated:
Name | RADIO
Nick | 0965
Nick | null
Nick | null
The above is then repeated for each person, with the other fields appearing correctly although duplicated on each row.
How can I consolidate these results to one row (exclude the ‘null’ results)?
mysql sql
asked Jan 19 at 9:14
TheMiddleTheMiddle
237
I’m working on a project and it’s become a bit more complicated than I’m able to deal with. I have a piece of SQL that inner joins to several tables. One of these tables returns communication means for each individual (telephone number, radio call sign, pager, for example).
Once I’ve implemented this code, the SQL (quite rightly) returns multiple records for each individual, depending on which comms fields they’ve got filled out. So a search for myself would return:
Name | COMM_VALUE | COMM_RID
Nick | 07123 456789 | 5
Nick | 0965 | 6
Nick | 328493 | 7
Where as, I need:
Name | Mobile | Radio | Pager
Nick | 07123 456789 | 0965 | 328493
Bob | etc etc etc
This is part of much larger table so my question is how do I add these columns to my existing SQL, I looked in to nested, subqueries, and unions but it’s an age since I did SQL and even my basic progress has been testing my capability.
I envisage something like the following but not sure of the keywords I need to achieve this:
Select
PRP.FIRSTNAME + ‘ ‘ + PRP.SURNAME AS FULLNAME,
RDP.POSTNAME,
RRP.REMARKS,
*/ NEW COLUMNS */
(SELECT COMM_VALUE FROM PERREC_COMMS WHERE COMM_RID = ‘6’) AS RADIO
The above (edited) code does yield results, but not quite as I anticipated:
Name | RADIO
Nick | 0965
Nick | null
Nick | null
The above is then repeated for each person, with the other fields appearing correctly although duplicated on each row.
How can I consolidate these results to one row (exclude the ‘null’ results)?
mysql sql
mysql sql
asked Jan 19 at 9:14
TheMiddleTheMiddle
237
asked Jan 19 at 9:14
TheMiddleTheMiddle
237
edited Jan 19 at 10:00
TheMiddle
asked Jan 19 at 9:14
TheMiddleTheMiddle
237
asked Jan 19 at 9:14
TheMiddleTheMiddle
237
asked Jan 19 at 9:14
TheMiddleTheMiddle
237
237
There's no pcomm_rid here.
– Strawberry
Jan 19 at 9:43
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 19 at 9:43
Typo, sorry. Will check your link
– TheMiddle
Jan 19 at 9:46
There's no comm _rid here either
– Strawberry
Jan 19 at 9:47
There is a comm_rid field in PERREC_COMMS. PERREC_COMMS is inner joined later in the statement. I’ve not included the full statement as it’s too long to retype. (SO is banned at work ;-( )
– TheMiddle
Jan 19 at 9:49
|
show 1 more comment
There's no pcomm_rid here.
– Strawberry
Jan 19 at 9:43
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 19 at 9:43
Typo, sorry. Will check your link
– TheMiddle
Jan 19 at 9:46
There's no comm _rid here either
– Strawberry
Jan 19 at 9:47
There is a comm_rid field in PERREC_COMMS. PERREC_COMMS is inner joined later in the statement. I’ve not included the full statement as it’s too long to retype. (SO is banned at work ;-( )
– TheMiddle
Jan 19 at 9:49
There's no pcomm_rid here.
– Strawberry
Jan 19 at 9:43
There's no pcomm_rid here.
– Strawberry
Jan 19 at 9:43
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 19 at 9:43
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 19 at 9:43
Typo, sorry. Will check your link
– TheMiddle
Jan 19 at 9:46
Typo, sorry. Will check your link
– TheMiddle
Jan 19 at 9:46
There's no comm _rid here either
– Strawberry
Jan 19 at 9:47
There's no comm _rid here either
– Strawberry
Jan 19 at 9:47
There is a comm_rid field in PERREC_COMMS. PERREC_COMMS is inner joined later in the statement. I’ve not included the full statement as it’s too long to retype. (SO is banned at work ;-( )
– TheMiddle
Jan 19 at 9:49
There is a comm_rid field in PERREC_COMMS. PERREC_COMMS is inner joined later in the statement. I’ve not included the full statement as it’s too long to retype. (SO is banned at work ;-( )
– TheMiddle
Jan 19 at 9:49
|
show 1 more comment
1 Answer
1
active
oldest
votes
You want to pivot the data. One method is conditional aggregation. For this purpose, I would recommend:
select name,
group_concat(case when comm_rid = 5 then comm_value end) as what_5_means,
group_concat(case when comm_rid = 6 then comm_value end) as radio,
group_concat(case when comm_rid = 7 then comm_value end) as whateve_7_means
from . . .
group by name;
I have no idea from the question what from clause to use.
This uses group_concat() rather than max() just in case someone has multiple means of communications with the same type.
answered Jan 19 at 10:46
Gordon LinoffGordon Linoff
769k35302403
Thanks, group_concat not recognised function, will try max()
– TheMiddle
Jan 19 at 10:54
@TheMiddle . . . Usemax(). However, your question is tagged mysql, andgroup_concat()has been in MySQL for a long time. You should be sure to tag the question with the database you are really using.
– Gordon Linoff
Jan 19 at 11:32
Thanks, max has helped and the case statement has got most of what I’ve needed done. I just need to remove the null values now but they are being stubborn!
– TheMiddle
Jan 19 at 11:35
Gordon, all sorted now. Thanks very much for the pointers
– TheMiddle
Jan 19 at 12:00
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
});
}
});
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%2f54265613%2ftranspose-part-of-sql-results-to-consolidate-records%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You want to pivot the data. One method is conditional aggregation. For this purpose, I would recommend:
select name,
group_concat(case when comm_rid = 5 then comm_value end) as what_5_means,
group_concat(case when comm_rid = 6 then comm_value end) as radio,
group_concat(case when comm_rid = 7 then comm_value end) as whateve_7_means
from . . .
group by name;
I have no idea from the question what from clause to use.
This uses group_concat() rather than max() just in case someone has multiple means of communications with the same type.
answered Jan 19 at 10:46
Gordon LinoffGordon Linoff
769k35302403
Thanks, group_concat not recognised function, will try max()
– TheMiddle
Jan 19 at 10:54
@TheMiddle . . . Usemax(). However, your question is tagged mysql, andgroup_concat()has been in MySQL for a long time. You should be sure to tag the question with the database you are really using.
– Gordon Linoff
Jan 19 at 11:32
Thanks, max has helped and the case statement has got most of what I’ve needed done. I just need to remove the null values now but they are being stubborn!
– TheMiddle
Jan 19 at 11:35
Gordon, all sorted now. Thanks very much for the pointers
– TheMiddle
Jan 19 at 12:00
add a comment |
You want to pivot the data. One method is conditional aggregation. For this purpose, I would recommend:
select name,
group_concat(case when comm_rid = 5 then comm_value end) as what_5_means,
group_concat(case when comm_rid = 6 then comm_value end) as radio,
group_concat(case when comm_rid = 7 then comm_value end) as whateve_7_means
from . . .
group by name;
I have no idea from the question what from clause to use.
This uses group_concat() rather than max() just in case someone has multiple means of communications with the same type.
answered Jan 19 at 10:46
Gordon LinoffGordon Linoff
769k35302403
Thanks, group_concat not recognised function, will try max()
– TheMiddle
Jan 19 at 10:54
@TheMiddle . . . Usemax(). However, your question is tagged mysql, andgroup_concat()has been in MySQL for a long time. You should be sure to tag the question with the database you are really using.
– Gordon Linoff
Jan 19 at 11:32
Thanks, max has helped and the case statement has got most of what I’ve needed done. I just need to remove the null values now but they are being stubborn!
– TheMiddle
Jan 19 at 11:35
Gordon, all sorted now. Thanks very much for the pointers
– TheMiddle
Jan 19 at 12:00
add a comment |
You want to pivot the data. One method is conditional aggregation. For this purpose, I would recommend:
select name,
group_concat(case when comm_rid = 5 then comm_value end) as what_5_means,
group_concat(case when comm_rid = 6 then comm_value end) as radio,
group_concat(case when comm_rid = 7 then comm_value end) as whateve_7_means
from . . .
group by name;
I have no idea from the question what from clause to use.
This uses group_concat() rather than max() just in case someone has multiple means of communications with the same type.
answered Jan 19 at 10:46
Gordon LinoffGordon Linoff
769k35302403
You want to pivot the data. One method is conditional aggregation. For this purpose, I would recommend:
select name,
group_concat(case when comm_rid = 5 then comm_value end) as what_5_means,
group_concat(case when comm_rid = 6 then comm_value end) as radio,
group_concat(case when comm_rid = 7 then comm_value end) as whateve_7_means
from . . .
group by name;
I have no idea from the question what from clause to use.
This uses group_concat() rather than max() just in case someone has multiple means of communications with the same type.
answered Jan 19 at 10:46
Gordon LinoffGordon Linoff
769k35302403
answered Jan 19 at 10:46
Gordon LinoffGordon Linoff
769k35302403
answered Jan 19 at 10:46
Gordon LinoffGordon Linoff
769k35302403
answered Jan 19 at 10:46
Gordon LinoffGordon Linoff
769k35302403
769k35302403
Thanks, group_concat not recognised function, will try max()
– TheMiddle
Jan 19 at 10:54
@TheMiddle . . . Usemax(). However, your question is tagged mysql, andgroup_concat()has been in MySQL for a long time. You should be sure to tag the question with the database you are really using.
– Gordon Linoff
Jan 19 at 11:32
Thanks, max has helped and the case statement has got most of what I’ve needed done. I just need to remove the null values now but they are being stubborn!
– TheMiddle
Jan 19 at 11:35
Gordon, all sorted now. Thanks very much for the pointers
– TheMiddle
Jan 19 at 12:00
add a comment |
Thanks, group_concat not recognised function, will try max()
– TheMiddle
Jan 19 at 10:54
@TheMiddle . . . Usemax(). However, your question is tagged mysql, andgroup_concat()has been in MySQL for a long time. You should be sure to tag the question with the database you are really using.
– Gordon Linoff
Jan 19 at 11:32
Thanks, max has helped and the case statement has got most of what I’ve needed done. I just need to remove the null values now but they are being stubborn!
– TheMiddle
Jan 19 at 11:35
Gordon, all sorted now. Thanks very much for the pointers
– TheMiddle
Jan 19 at 12:00
Thanks, group_concat not recognised function, will try max()
– TheMiddle
Jan 19 at 10:54
Thanks, group_concat not recognised function, will try max()
– TheMiddle
Jan 19 at 10:54
@TheMiddle . . . Use
max(). However, your question is tagged mysql, and group_concat() has been in MySQL for a long time. You should be sure to tag the question with the database you are really using.– Gordon Linoff
Jan 19 at 11:32
@TheMiddle . . . Use
max(). However, your question is tagged mysql, and group_concat() has been in MySQL for a long time. You should be sure to tag the question with the database you are really using.– Gordon Linoff
Jan 19 at 11:32
Thanks, max has helped and the case statement has got most of what I’ve needed done. I just need to remove the null values now but they are being stubborn!
– TheMiddle
Jan 19 at 11:35
Thanks, max has helped and the case statement has got most of what I’ve needed done. I just need to remove the null values now but they are being stubborn!
– TheMiddle
Jan 19 at 11:35
Gordon, all sorted now. Thanks very much for the pointers
– TheMiddle
Jan 19 at 12:00
Gordon, all sorted now. Thanks very much for the pointers
– TheMiddle
Jan 19 at 12:00
add a comment |
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.
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%2f54265613%2ftranspose-part-of-sql-results-to-consolidate-records%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
There's no pcomm_rid here.
– Strawberry
Jan 19 at 9:43
See meta.stackoverflow.com/questions/333952/…
– Strawberry
Jan 19 at 9:43
Typo, sorry. Will check your link
– TheMiddle
Jan 19 at 9:46
There's no comm _rid here either
– Strawberry
Jan 19 at 9:47
There is a comm_rid field in PERREC_COMMS. PERREC_COMMS is inner joined later in the statement. I’ve not included the full statement as it’s too long to retype. (SO is banned at work ;-( )
– TheMiddle
Jan 19 at 9:49