Horizontally 'collapse' two columns with matching substrings into oneHow to check if a column exists in a SQL Server table?SQL Server - Multi-Column substring matchingConcatenate two columns valuesHow to write UPDATE query to use two columns from other table that are from same random row?Comparing two tables for each column of each row and result as log variableIs there a better way to apply isnull to all columns than what I'm doing?how to combine two columns into oneGROUP BY to combine/concat a columnRename column SQL Server 2008Convert two tables rows to columns in SQL Server using Pivot
I have two helper functions that are the exact same, one executes and one doesn't. How come?
One hour 10 min layover in Newark; International -> Domestic connection. Enough time to clear customs?
Does the Scrying spell require you to have a clear path to the target in order to work?
Calculus Books, preferably Soviet.
Using font to highlight a god's speech in dialogue
Is torque as fundamental a concept as force?
Why does this syntax outputs an error under METAFUN/METAPOST?
Can there be plants on the dark side of a tidally locked world?
In chocolate terminology, what is the name of thinly sliced leaf-shaped toppings made from hot, smooth chocolate, used to form flower petals?
Are there photos of the Apollo LM showing disturbed lunar soil resulting from descent engine exhaust?
What is the converted mana cost of land cards?
Strange LockTime values in Electrum transactions?
Solve this icositetragram
Tiny image scraper for xkcd.com
Visiting girlfriend in the USA
What is a "fat pointer" in Rust?
How did Gollum know Sauron was gathering the Haradrim to make war?
Why would a Intel 8080 chip be destroyed if +12 V is connected before -5 V?
Can an intercepting fighter jet force a small propeller aircraft down without completely destroying it?
How do we know if a dialogue sounds unnatural without asking for feedback?
Can my UK debt be collected because I have to return to US?
Divide Numbers by 0
Is it rude to ask my opponent to resign an online game when they have a lost endgame?
How to find better food in airports
Horizontally 'collapse' two columns with matching substrings into one
How to check if a column exists in a SQL Server table?SQL Server - Multi-Column substring matchingConcatenate two columns valuesHow to write UPDATE query to use two columns from other table that are from same random row?Comparing two tables for each column of each row and result as log variableIs there a better way to apply isnull to all columns than what I'm doing?how to combine two columns into oneGROUP BY to combine/concat a columnRename column SQL Server 2008Convert two tables rows to columns in SQL Server using Pivot
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
My query results in a table that looks like this:
Unit25 Unit26 Seconds25 Seconds26
A2501 null 383042 null
A2502 A2602 354554 35147
A2503 A2603 344021 33388
A2504 null 359453 null
A2505 null 16702 null
A2507 null 14784 null
null A2608 null 16997
A2509 A2609 13092 358893
null A2610 null 291256
A2511 A2611 18770 369082
A2512 A2612 8647 null
A2513 null 65214 null
A25s are matched to corresponding A26s by a substring of their last two digits.
However, I would like to combine those A25 and A26 columns into something like this:
Unit TotalSeconds
01 383042
02 389701
03 377409
04 359453
05 16702
07 14784
08 16997
09 371985
10 291256
11 387852
12 8647
13 65214
I can get TotalSeconds by using ISNULL to turn those nulls into 0s and adding Seconds25 and Seconds26 together, but I'm not so sure how to turn Unit25 and Unit26 into one column of substrings. Any advice?
sql-server-2008
asked Mar 27 at 14:09
dhwudhwu
91 bronze badge
add a comment |
My query results in a table that looks like this:
Unit25 Unit26 Seconds25 Seconds26
A2501 null 383042 null
A2502 A2602 354554 35147
A2503 A2603 344021 33388
A2504 null 359453 null
A2505 null 16702 null
A2507 null 14784 null
null A2608 null 16997
A2509 A2609 13092 358893
null A2610 null 291256
A2511 A2611 18770 369082
A2512 A2612 8647 null
A2513 null 65214 null
A25s are matched to corresponding A26s by a substring of their last two digits.
However, I would like to combine those A25 and A26 columns into something like this:
Unit TotalSeconds
01 383042
02 389701
03 377409
04 359453
05 16702
07 14784
08 16997
09 371985
10 291256
11 387852
12 8647
13 65214
I can get TotalSeconds by using ISNULL to turn those nulls into 0s and adding Seconds25 and Seconds26 together, but I'm not so sure how to turn Unit25 and Unit26 into one column of substrings. Any advice?
sql-server-2008
asked Mar 27 at 14:09
dhwudhwu
91 bronze badge
add a comment |
My query results in a table that looks like this:
Unit25 Unit26 Seconds25 Seconds26
A2501 null 383042 null
A2502 A2602 354554 35147
A2503 A2603 344021 33388
A2504 null 359453 null
A2505 null 16702 null
A2507 null 14784 null
null A2608 null 16997
A2509 A2609 13092 358893
null A2610 null 291256
A2511 A2611 18770 369082
A2512 A2612 8647 null
A2513 null 65214 null
A25s are matched to corresponding A26s by a substring of their last two digits.
However, I would like to combine those A25 and A26 columns into something like this:
Unit TotalSeconds
01 383042
02 389701
03 377409
04 359453
05 16702
07 14784
08 16997
09 371985
10 291256
11 387852
12 8647
13 65214
I can get TotalSeconds by using ISNULL to turn those nulls into 0s and adding Seconds25 and Seconds26 together, but I'm not so sure how to turn Unit25 and Unit26 into one column of substrings. Any advice?
sql-server-2008
asked Mar 27 at 14:09
dhwudhwu
91 bronze badge
My query results in a table that looks like this:
Unit25 Unit26 Seconds25 Seconds26
A2501 null 383042 null
A2502 A2602 354554 35147
A2503 A2603 344021 33388
A2504 null 359453 null
A2505 null 16702 null
A2507 null 14784 null
null A2608 null 16997
A2509 A2609 13092 358893
null A2610 null 291256
A2511 A2611 18770 369082
A2512 A2612 8647 null
A2513 null 65214 null
A25s are matched to corresponding A26s by a substring of their last two digits.
However, I would like to combine those A25 and A26 columns into something like this:
Unit TotalSeconds
01 383042
02 389701
03 377409
04 359453
05 16702
07 14784
08 16997
09 371985
10 291256
11 387852
12 8647
13 65214
I can get TotalSeconds by using ISNULL to turn those nulls into 0s and adding Seconds25 and Seconds26 together, but I'm not so sure how to turn Unit25 and Unit26 into one column of substrings. Any advice?
sql-server-2008
sql-server-2008
asked Mar 27 at 14:09
dhwudhwu
91 bronze badge
asked Mar 27 at 14:09
dhwudhwu
91 bronze badge
asked Mar 27 at 14:09
dhwudhwu
91 bronze badge
asked Mar 27 at 14:09
dhwudhwu
91 bronze badge
asked Mar 27 at 14:09
dhwudhwu
91 bronze badge
91 bronze badge
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Coalesce (or isnull) the two columns together so you always get a value, it doesn't matter which one. Get the length of the data, and then substring the last two characters, getting two characters starting at length -1. Eg - "A2501" is length five, so you want to substring from position 4 to get 01.
SUBSTRING(Coalesce(Unit25, Unit26), Len(Coalesce(Unit25, Unit26)) - 1, 2)
answered Mar 28 at 1:36
DancingFoolDancingFool
1,2471 gold badge7 silver badges10 bronze badges
add a comment |
You can try this.
select substring(Coalesce(unit25,unit26),4,2) as units,isnull(seconds25,0)+isnull(seconds26,0) from @t
answered Apr 20 at 9:20
Red DevilRed Devil
1,1261 gold badge9 silver badges22 bronze badges
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%2f55379301%2fhorizontally-collapse-two-columns-with-matching-substrings-into-one%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
Coalesce (or isnull) the two columns together so you always get a value, it doesn't matter which one. Get the length of the data, and then substring the last two characters, getting two characters starting at length -1. Eg - "A2501" is length five, so you want to substring from position 4 to get 01.
SUBSTRING(Coalesce(Unit25, Unit26), Len(Coalesce(Unit25, Unit26)) - 1, 2)
answered Mar 28 at 1:36
DancingFoolDancingFool
1,2471 gold badge7 silver badges10 bronze badges
add a comment |
Coalesce (or isnull) the two columns together so you always get a value, it doesn't matter which one. Get the length of the data, and then substring the last two characters, getting two characters starting at length -1. Eg - "A2501" is length five, so you want to substring from position 4 to get 01.
SUBSTRING(Coalesce(Unit25, Unit26), Len(Coalesce(Unit25, Unit26)) - 1, 2)
answered Mar 28 at 1:36
DancingFoolDancingFool
1,2471 gold badge7 silver badges10 bronze badges
add a comment |
Coalesce (or isnull) the two columns together so you always get a value, it doesn't matter which one. Get the length of the data, and then substring the last two characters, getting two characters starting at length -1. Eg - "A2501" is length five, so you want to substring from position 4 to get 01.
SUBSTRING(Coalesce(Unit25, Unit26), Len(Coalesce(Unit25, Unit26)) - 1, 2)
answered Mar 28 at 1:36
DancingFoolDancingFool
1,2471 gold badge7 silver badges10 bronze badges
Coalesce (or isnull) the two columns together so you always get a value, it doesn't matter which one. Get the length of the data, and then substring the last two characters, getting two characters starting at length -1. Eg - "A2501" is length five, so you want to substring from position 4 to get 01.
SUBSTRING(Coalesce(Unit25, Unit26), Len(Coalesce(Unit25, Unit26)) - 1, 2)
answered Mar 28 at 1:36
DancingFoolDancingFool
1,2471 gold badge7 silver badges10 bronze badges
answered Mar 28 at 1:36
DancingFoolDancingFool
1,2471 gold badge7 silver badges10 bronze badges
answered Mar 28 at 1:36
DancingFoolDancingFool
1,2471 gold badge7 silver badges10 bronze badges
answered Mar 28 at 1:36
DancingFoolDancingFool
1,2471 gold badge7 silver badges10 bronze badges
1,2471 gold badge7 silver badges10 bronze badges
add a comment |
add a comment |
You can try this.
select substring(Coalesce(unit25,unit26),4,2) as units,isnull(seconds25,0)+isnull(seconds26,0) from @t
answered Apr 20 at 9:20
Red DevilRed Devil
1,1261 gold badge9 silver badges22 bronze badges
add a comment |
You can try this.
select substring(Coalesce(unit25,unit26),4,2) as units,isnull(seconds25,0)+isnull(seconds26,0) from @t
answered Apr 20 at 9:20
Red DevilRed Devil
1,1261 gold badge9 silver badges22 bronze badges
add a comment |
You can try this.
select substring(Coalesce(unit25,unit26),4,2) as units,isnull(seconds25,0)+isnull(seconds26,0) from @t
answered Apr 20 at 9:20
Red DevilRed Devil
1,1261 gold badge9 silver badges22 bronze badges
You can try this.
select substring(Coalesce(unit25,unit26),4,2) as units,isnull(seconds25,0)+isnull(seconds26,0) from @t
answered Apr 20 at 9:20
Red DevilRed Devil
1,1261 gold badge9 silver badges22 bronze badges
answered Apr 20 at 9:20
Red DevilRed Devil
1,1261 gold badge9 silver badges22 bronze badges
answered Apr 20 at 9:20
Red DevilRed Devil
1,1261 gold badge9 silver badges22 bronze badges
answered Apr 20 at 9:20
Red DevilRed Devil
1,1261 gold badge9 silver badges22 bronze badges
1,1261 gold badge9 silver badges22 bronze badges
add a comment |
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%2f55379301%2fhorizontally-collapse-two-columns-with-matching-substrings-into-one%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
