Netezza- Concatenate Different Values from Single Column based on Order from another Column
I am trying to do a column concatenate based on order of Sample Counter and by ID field. Below is an example of the data
**Heat ID** **Sample Type** **Sample Counter**
466170 T1 2
466170 L0 3
466170 C1 4
466170 V2 1
580910 C1 1
580910 L0 2
580910 T1 3
This is what I want below. So I want it concatenated by ascending sample counter I guess you could say.
**Heat ID** **Concat Code**
466170 V2_T1_L0_C1
580910 C1_L0_T1
The data is structured so that not every heat ID will have the same amount of Sample Types and the Sample Types are in different order. Sample counter is when the different Sample Type is used (The Order). Any help would be greatly appreciated. Thank you!
sql netezza
edited Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
asked Apr 25 '16 at 17:52
Drew
571210
add a comment |
I am trying to do a column concatenate based on order of Sample Counter and by ID field. Below is an example of the data
**Heat ID** **Sample Type** **Sample Counter**
466170 T1 2
466170 L0 3
466170 C1 4
466170 V2 1
580910 C1 1
580910 L0 2
580910 T1 3
This is what I want below. So I want it concatenated by ascending sample counter I guess you could say.
**Heat ID** **Concat Code**
466170 V2_T1_L0_C1
580910 C1_L0_T1
The data is structured so that not every heat ID will have the same amount of Sample Types and the Sample Types are in different order. Sample counter is when the different Sample Type is used (The Order). Any help would be greatly appreciated. Thank you!
sql netezza
edited Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
asked Apr 25 '16 at 17:52
Drew
571210
Please tag your question with the database you are using.
– Gordon Linoff
Apr 25 '16 at 17:56
What rdbms are you using?
– Mureinik
Apr 25 '16 at 17:57
add a comment |
I am trying to do a column concatenate based on order of Sample Counter and by ID field. Below is an example of the data
**Heat ID** **Sample Type** **Sample Counter**
466170 T1 2
466170 L0 3
466170 C1 4
466170 V2 1
580910 C1 1
580910 L0 2
580910 T1 3
This is what I want below. So I want it concatenated by ascending sample counter I guess you could say.
**Heat ID** **Concat Code**
466170 V2_T1_L0_C1
580910 C1_L0_T1
The data is structured so that not every heat ID will have the same amount of Sample Types and the Sample Types are in different order. Sample counter is when the different Sample Type is used (The Order). Any help would be greatly appreciated. Thank you!
sql netezza
edited Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
asked Apr 25 '16 at 17:52
Drew
571210
I am trying to do a column concatenate based on order of Sample Counter and by ID field. Below is an example of the data
**Heat ID** **Sample Type** **Sample Counter**
466170 T1 2
466170 L0 3
466170 C1 4
466170 V2 1
580910 C1 1
580910 L0 2
580910 T1 3
This is what I want below. So I want it concatenated by ascending sample counter I guess you could say.
**Heat ID** **Concat Code**
466170 V2_T1_L0_C1
580910 C1_L0_T1
The data is structured so that not every heat ID will have the same amount of Sample Types and the Sample Types are in different order. Sample counter is when the different Sample Type is used (The Order). Any help would be greatly appreciated. Thank you!
sql netezza
sql netezza
edited Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
asked Apr 25 '16 at 17:52
Drew
571210
edited Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
asked Apr 25 '16 at 17:52
Drew
571210
edited Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
edited Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
edited Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
758k35291399
asked Apr 25 '16 at 17:52
Drew
571210
asked Apr 25 '16 at 17:52
Drew
571210
asked Apr 25 '16 at 17:52
Drew
571210
571210
Please tag your question with the database you are using.
– Gordon Linoff
Apr 25 '16 at 17:56
What rdbms are you using?
– Mureinik
Apr 25 '16 at 17:57
add a comment |
Please tag your question with the database you are using.
– Gordon Linoff
Apr 25 '16 at 17:56
What rdbms are you using?
– Mureinik
Apr 25 '16 at 17:57
Please tag your question with the database you are using.
– Gordon Linoff
Apr 25 '16 at 17:56
Please tag your question with the database you are using.
– Gordon Linoff
Apr 25 '16 at 17:56
What rdbms are you using?
– Mureinik
Apr 25 '16 at 17:57
What rdbms are you using?
– Mureinik
Apr 25 '16 at 17:57
add a comment |
1 Answer
1
active
oldest
votes
There are several ways to approach this. String aggregation is generally rather database specific. However, the counter column allows conditional aggregation instead:
select heatid,
(max(case when samplecounter = 1 then sample_type else '' end) ||
max(case when samplecounter = 2 then '_' || sample_type else '' end) ||
max(case when samplecounter = 3 then '_' || sample_type else '' end) ||
max(case when samplecounter = 4 then '_' || sample_type else '' end) ||
max(case when samplecounter = 5 then '_' || sample_type else '' end)
) as concat_code
from t
group by heatid;
Note you need enough conditional statements for the maximum sample counter.
Also, some databases spell || as + or even require the explicit use of a concat() function.
answered Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
That worked perfectly, thank you so much Gordon!
– Drew
Apr 25 '16 at 18:09
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%2f36847679%2fnetezza-concatenate-different-values-from-single-column-based-on-order-from-ano%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
There are several ways to approach this. String aggregation is generally rather database specific. However, the counter column allows conditional aggregation instead:
select heatid,
(max(case when samplecounter = 1 then sample_type else '' end) ||
max(case when samplecounter = 2 then '_' || sample_type else '' end) ||
max(case when samplecounter = 3 then '_' || sample_type else '' end) ||
max(case when samplecounter = 4 then '_' || sample_type else '' end) ||
max(case when samplecounter = 5 then '_' || sample_type else '' end)
) as concat_code
from t
group by heatid;
Note you need enough conditional statements for the maximum sample counter.
Also, some databases spell || as + or even require the explicit use of a concat() function.
answered Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
That worked perfectly, thank you so much Gordon!
– Drew
Apr 25 '16 at 18:09
add a comment |
There are several ways to approach this. String aggregation is generally rather database specific. However, the counter column allows conditional aggregation instead:
select heatid,
(max(case when samplecounter = 1 then sample_type else '' end) ||
max(case when samplecounter = 2 then '_' || sample_type else '' end) ||
max(case when samplecounter = 3 then '_' || sample_type else '' end) ||
max(case when samplecounter = 4 then '_' || sample_type else '' end) ||
max(case when samplecounter = 5 then '_' || sample_type else '' end)
) as concat_code
from t
group by heatid;
Note you need enough conditional statements for the maximum sample counter.
Also, some databases spell || as + or even require the explicit use of a concat() function.
answered Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
That worked perfectly, thank you so much Gordon!
– Drew
Apr 25 '16 at 18:09
add a comment |
There are several ways to approach this. String aggregation is generally rather database specific. However, the counter column allows conditional aggregation instead:
select heatid,
(max(case when samplecounter = 1 then sample_type else '' end) ||
max(case when samplecounter = 2 then '_' || sample_type else '' end) ||
max(case when samplecounter = 3 then '_' || sample_type else '' end) ||
max(case when samplecounter = 4 then '_' || sample_type else '' end) ||
max(case when samplecounter = 5 then '_' || sample_type else '' end)
) as concat_code
from t
group by heatid;
Note you need enough conditional statements for the maximum sample counter.
Also, some databases spell || as + or even require the explicit use of a concat() function.
answered Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
There are several ways to approach this. String aggregation is generally rather database specific. However, the counter column allows conditional aggregation instead:
select heatid,
(max(case when samplecounter = 1 then sample_type else '' end) ||
max(case when samplecounter = 2 then '_' || sample_type else '' end) ||
max(case when samplecounter = 3 then '_' || sample_type else '' end) ||
max(case when samplecounter = 4 then '_' || sample_type else '' end) ||
max(case when samplecounter = 5 then '_' || sample_type else '' end)
) as concat_code
from t
group by heatid;
Note you need enough conditional statements for the maximum sample counter.
Also, some databases spell || as + or even require the explicit use of a concat() function.
answered Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
answered Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
answered Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
answered Apr 25 '16 at 18:02
Gordon Linoff
758k35291399
758k35291399
That worked perfectly, thank you so much Gordon!
– Drew
Apr 25 '16 at 18:09
add a comment |
That worked perfectly, thank you so much Gordon!
– Drew
Apr 25 '16 at 18:09
That worked perfectly, thank you so much Gordon!
– Drew
Apr 25 '16 at 18:09
That worked perfectly, thank you so much Gordon!
– Drew
Apr 25 '16 at 18:09
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f36847679%2fnetezza-concatenate-different-values-from-single-column-based-on-order-from-ano%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
Please tag your question with the database you are using.
– Gordon Linoff
Apr 25 '16 at 17:56
What rdbms are you using?
– Mureinik
Apr 25 '16 at 17:57