SQL add new line if value is in second column also
So I am trying to pull the data from the table to give me each score the person has and if they have two scores I would like it to be on a new line with the second score. If the user has no scores I don't want anything returned. My query returns the first score if the user has one and if they don't it returns the second one. But if the user has two scores is where i'm not sure how to return that one on a new line.
table 1
+---------+--------+--------+
| name | score1 | score2 |
+---------+--------+--------+
| jim | null | 87 |
| doug | 21 | 45 |
| brandon | null | null |
| susy | 11 | null |
+---------+--------+--------+
The result my query gives is
+------+----+
| jim | 87 |
| doug | 21 |
| susy | 11 |
+------+----+
Wanted output
+------+----+
| jim | 87 |
| doug | 21 |
| doug | 45 |
| susy | 11 |
+------+----+
The query I wrote is
SELECT
name
,COALESCE(score1, score2)
FROM
table
WHERE
score1 IS NOT NULL
OR score2 IS NOT NULL
ORDER BY
name;
sql
sql-server edited Nov 21 '18 at 21:56
Eric Brandt
2,3771724
asked Nov 21 '18 at 21:44
Brandon BrownBrandon Brown
144
add a comment |
So I am trying to pull the data from the table to give me each score the person has and if they have two scores I would like it to be on a new line with the second score. If the user has no scores I don't want anything returned. My query returns the first score if the user has one and if they don't it returns the second one. But if the user has two scores is where i'm not sure how to return that one on a new line.
table 1
+---------+--------+--------+
| name | score1 | score2 |
+---------+--------+--------+
| jim | null | 87 |
| doug | 21 | 45 |
| brandon | null | null |
| susy | 11 | null |
+---------+--------+--------+
The result my query gives is
+------+----+
| jim | 87 |
| doug | 21 |
| susy | 11 |
+------+----+
Wanted output
+------+----+
| jim | 87 |
| doug | 21 |
| doug | 45 |
| susy | 11 |
+------+----+
The query I wrote is
SELECT
name
,COALESCE(score1, score2)
FROM
table
WHERE
score1 IS NOT NULL
OR score2 IS NOT NULL
ORDER BY
name;
sql
sql-server edited Nov 21 '18 at 21:56
Eric Brandt
2,3771724
asked Nov 21 '18 at 21:44
Brandon BrownBrandon Brown
144
UseAPPLYorUNPIVOT. e.g.SELECT T.Name, C.Score FROM myTable AS T CROSS APPLY (VALUES (T.Score1), (T.Score2)) AS C(Score) WHERE C.Score IS NOT NULL;
– ZLK
Nov 21 '18 at 21:45
On an unrelated note, have a look at this post for some tips and tools for formatting your posts.
– Eric Brandt
Nov 21 '18 at 21:56
@ZLK I didn't know you could cross apply to VALUES from the original table. Thanks for teaching me something neat today.
– SteveB
Nov 21 '18 at 22:02
add a comment |
So I am trying to pull the data from the table to give me each score the person has and if they have two scores I would like it to be on a new line with the second score. If the user has no scores I don't want anything returned. My query returns the first score if the user has one and if they don't it returns the second one. But if the user has two scores is where i'm not sure how to return that one on a new line.
table 1
+---------+--------+--------+
| name | score1 | score2 |
+---------+--------+--------+
| jim | null | 87 |
| doug | 21 | 45 |
| brandon | null | null |
| susy | 11 | null |
+---------+--------+--------+
The result my query gives is
+------+----+
| jim | 87 |
| doug | 21 |
| susy | 11 |
+------+----+
Wanted output
+------+----+
| jim | 87 |
| doug | 21 |
| doug | 45 |
| susy | 11 |
+------+----+
The query I wrote is
SELECT
name
,COALESCE(score1, score2)
FROM
table
WHERE
score1 IS NOT NULL
OR score2 IS NOT NULL
ORDER BY
name;
sql
sql-server edited Nov 21 '18 at 21:56
Eric Brandt
2,3771724
asked Nov 21 '18 at 21:44
Brandon BrownBrandon Brown
144
So I am trying to pull the data from the table to give me each score the person has and if they have two scores I would like it to be on a new line with the second score. If the user has no scores I don't want anything returned. My query returns the first score if the user has one and if they don't it returns the second one. But if the user has two scores is where i'm not sure how to return that one on a new line.
table 1
+---------+--------+--------+
| name | score1 | score2 |
+---------+--------+--------+
| jim | null | 87 |
| doug | 21 | 45 |
| brandon | null | null |
| susy | 11 | null |
+---------+--------+--------+
The result my query gives is
+------+----+
| jim | 87 |
| doug | 21 |
| susy | 11 |
+------+----+
Wanted output
+------+----+
| jim | 87 |
| doug | 21 |
| doug | 45 |
| susy | 11 |
+------+----+
The query I wrote is
SELECT
name
,COALESCE(score1, score2)
FROM
table
WHERE
score1 IS NOT NULL
OR score2 IS NOT NULL
ORDER BY
name;
sql
sql-server sql
sql-server edited Nov 21 '18 at 21:56
Eric Brandt
2,3771724
asked Nov 21 '18 at 21:44
Brandon BrownBrandon Brown
144
edited Nov 21 '18 at 21:56
Eric Brandt
2,3771724
asked Nov 21 '18 at 21:44
Brandon BrownBrandon Brown
144
edited Nov 21 '18 at 21:56
Eric Brandt
2,3771724
edited Nov 21 '18 at 21:56
Eric Brandt
2,3771724
edited Nov 21 '18 at 21:56
Eric Brandt
2,3771724
2,3771724
asked Nov 21 '18 at 21:44
Brandon BrownBrandon Brown
144
asked Nov 21 '18 at 21:44
Brandon BrownBrandon Brown
144
asked Nov 21 '18 at 21:44
Brandon BrownBrandon Brown
144
144
UseAPPLYorUNPIVOT. e.g.SELECT T.Name, C.Score FROM myTable AS T CROSS APPLY (VALUES (T.Score1), (T.Score2)) AS C(Score) WHERE C.Score IS NOT NULL;
– ZLK
Nov 21 '18 at 21:45
On an unrelated note, have a look at this post for some tips and tools for formatting your posts.
– Eric Brandt
Nov 21 '18 at 21:56
@ZLK I didn't know you could cross apply to VALUES from the original table. Thanks for teaching me something neat today.
– SteveB
Nov 21 '18 at 22:02
add a comment |
UseAPPLYorUNPIVOT. e.g.SELECT T.Name, C.Score FROM myTable AS T CROSS APPLY (VALUES (T.Score1), (T.Score2)) AS C(Score) WHERE C.Score IS NOT NULL;
– ZLK
Nov 21 '18 at 21:45
On an unrelated note, have a look at this post for some tips and tools for formatting your posts.
– Eric Brandt
Nov 21 '18 at 21:56
@ZLK I didn't know you could cross apply to VALUES from the original table. Thanks for teaching me something neat today.
– SteveB
Nov 21 '18 at 22:02
Use
APPLY or UNPIVOT. e.g. SELECT T.Name, C.Score FROM myTable AS T CROSS APPLY (VALUES (T.Score1), (T.Score2)) AS C(Score) WHERE C.Score IS NOT NULL;– ZLK
Nov 21 '18 at 21:45
Use
APPLY or UNPIVOT. e.g. SELECT T.Name, C.Score FROM myTable AS T CROSS APPLY (VALUES (T.Score1), (T.Score2)) AS C(Score) WHERE C.Score IS NOT NULL;– ZLK
Nov 21 '18 at 21:45
On an unrelated note, have a look at this post for some tips and tools for formatting your posts.
– Eric Brandt
Nov 21 '18 at 21:56
On an unrelated note, have a look at this post for some tips and tools for formatting your posts.
– Eric Brandt
Nov 21 '18 at 21:56
@ZLK I didn't know you could cross apply to VALUES from the original table. Thanks for teaching me something neat today.
– SteveB
Nov 21 '18 at 22:02
@ZLK I didn't know you could cross apply to VALUES from the original table. Thanks for teaching me something neat today.
– SteveB
Nov 21 '18 at 22:02
add a comment |
2 Answers
2
active
oldest
votes
Treat this as two separate queries and combine the results together with UNION ALL. You'll want UNION ALL in this case and not just UNION so you get two rows returned in the case where the person has the same score in both columns.
SELECT name, Score1 as score
FROM table1
WHERE Score1 IS NOT NULL
UNION ALL
SELECT name, Score2 as score
FROM table1
WHERE Score2 IS NOT NULL
ORDER BY name, score;
answered Nov 21 '18 at 21:57
Joe StefanelliJoe Stefanelli
110k13191207
add a comment |
I would recommend cross apply:
SELECT t.name, v.score
FROM table t CROSS APPLY
(VALUES (score1), (score2)) v(score)
WHERE v.score IS NOT NULL
ORDER BY name;
This is usually the most efficient way to unpivot data in SQL Server.
answered Nov 21 '18 at 22:36
Gordon LinoffGordon Linoff
762k35294399
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%2f53420892%2fsql-add-new-line-if-value-is-in-second-column-also%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
Treat this as two separate queries and combine the results together with UNION ALL. You'll want UNION ALL in this case and not just UNION so you get two rows returned in the case where the person has the same score in both columns.
SELECT name, Score1 as score
FROM table1
WHERE Score1 IS NOT NULL
UNION ALL
SELECT name, Score2 as score
FROM table1
WHERE Score2 IS NOT NULL
ORDER BY name, score;
answered Nov 21 '18 at 21:57
Joe StefanelliJoe Stefanelli
110k13191207
add a comment |
Treat this as two separate queries and combine the results together with UNION ALL. You'll want UNION ALL in this case and not just UNION so you get two rows returned in the case where the person has the same score in both columns.
SELECT name, Score1 as score
FROM table1
WHERE Score1 IS NOT NULL
UNION ALL
SELECT name, Score2 as score
FROM table1
WHERE Score2 IS NOT NULL
ORDER BY name, score;
answered Nov 21 '18 at 21:57
Joe StefanelliJoe Stefanelli
110k13191207
add a comment |
Treat this as two separate queries and combine the results together with UNION ALL. You'll want UNION ALL in this case and not just UNION so you get two rows returned in the case where the person has the same score in both columns.
SELECT name, Score1 as score
FROM table1
WHERE Score1 IS NOT NULL
UNION ALL
SELECT name, Score2 as score
FROM table1
WHERE Score2 IS NOT NULL
ORDER BY name, score;
answered Nov 21 '18 at 21:57
Joe StefanelliJoe Stefanelli
110k13191207
Treat this as two separate queries and combine the results together with UNION ALL. You'll want UNION ALL in this case and not just UNION so you get two rows returned in the case where the person has the same score in both columns.
SELECT name, Score1 as score
FROM table1
WHERE Score1 IS NOT NULL
UNION ALL
SELECT name, Score2 as score
FROM table1
WHERE Score2 IS NOT NULL
ORDER BY name, score;
answered Nov 21 '18 at 21:57
Joe StefanelliJoe Stefanelli
110k13191207
answered Nov 21 '18 at 21:57
Joe StefanelliJoe Stefanelli
110k13191207
answered Nov 21 '18 at 21:57
Joe StefanelliJoe Stefanelli
110k13191207
answered Nov 21 '18 at 21:57
Joe StefanelliJoe Stefanelli
110k13191207
110k13191207
add a comment |
add a comment |
I would recommend cross apply:
SELECT t.name, v.score
FROM table t CROSS APPLY
(VALUES (score1), (score2)) v(score)
WHERE v.score IS NOT NULL
ORDER BY name;
This is usually the most efficient way to unpivot data in SQL Server.
answered Nov 21 '18 at 22:36
Gordon LinoffGordon Linoff
762k35294399
add a comment |
I would recommend cross apply:
SELECT t.name, v.score
FROM table t CROSS APPLY
(VALUES (score1), (score2)) v(score)
WHERE v.score IS NOT NULL
ORDER BY name;
This is usually the most efficient way to unpivot data in SQL Server.
answered Nov 21 '18 at 22:36
Gordon LinoffGordon Linoff
762k35294399
add a comment |
I would recommend cross apply:
SELECT t.name, v.score
FROM table t CROSS APPLY
(VALUES (score1), (score2)) v(score)
WHERE v.score IS NOT NULL
ORDER BY name;
This is usually the most efficient way to unpivot data in SQL Server.
answered Nov 21 '18 at 22:36
Gordon LinoffGordon Linoff
762k35294399
I would recommend cross apply:
SELECT t.name, v.score
FROM table t CROSS APPLY
(VALUES (score1), (score2)) v(score)
WHERE v.score IS NOT NULL
ORDER BY name;
This is usually the most efficient way to unpivot data in SQL Server.
answered Nov 21 '18 at 22:36
Gordon LinoffGordon Linoff
762k35294399
answered Nov 21 '18 at 22:36
Gordon LinoffGordon Linoff
762k35294399
answered Nov 21 '18 at 22:36
Gordon LinoffGordon Linoff
762k35294399
answered Nov 21 '18 at 22:36
Gordon LinoffGordon Linoff
762k35294399
762k35294399
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%2f53420892%2fsql-add-new-line-if-value-is-in-second-column-also%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
Use
APPLYorUNPIVOT. e.g.SELECT T.Name, C.Score FROM myTable AS T CROSS APPLY (VALUES (T.Score1), (T.Score2)) AS C(Score) WHERE C.Score IS NOT NULL;– ZLK
Nov 21 '18 at 21:45
On an unrelated note, have a look at this post for some tips and tools for formatting your posts.
– Eric Brandt
Nov 21 '18 at 21:56
@ZLK I didn't know you could cross apply to VALUES from the original table. Thanks for teaching me something neat today.
– SteveB
Nov 21 '18 at 22:02