How to return columns conditionally in SQL Server query?
Say I have columns is_return_foo, is_return_bar and is_return_baz.
I need to return the foo, bar, baz columns if any of the above are respectively set to true...
Is CASE WHEN the best option?
Something like:
SELECT
CASE is_return_foo WHEN true THEN foo ELSE null
CASE is_return_bar WHEN true THEN bar ELSE null
CASE is_return_baz WHEN true THEN baz ELSE null
another_column
FROM
my_table
Update
Basically I want to return columns based on on/off flags. So if flag A is on then return the column A value, if flag B is on then return column B value.
Maybe we could say based on permissions but more fine grained.
So say you have email message with to, from, body, headers, read, read time.
So a standard user will only see to from, body, and a premium customer might be configured to also read headers, read and read time.
But would.like to do ot per column instead of group of columns.
If it was group of columns then we could easily say CASE WHEN premium THEN headers, read, read time.
Update 2
I think we can do group based "permissions" so if you are a silver member you only see some fields, but if you are gold member you see all fields.
sql-server tsql case-expression edited Feb 1 at 17:23
marc_s
580k13011181266
asked Nov 27 '18 at 15:39
user432024user432024
1,54742855
add a comment |
Say I have columns is_return_foo, is_return_bar and is_return_baz.
I need to return the foo, bar, baz columns if any of the above are respectively set to true...
Is CASE WHEN the best option?
Something like:
SELECT
CASE is_return_foo WHEN true THEN foo ELSE null
CASE is_return_bar WHEN true THEN bar ELSE null
CASE is_return_baz WHEN true THEN baz ELSE null
another_column
FROM
my_table
Update
Basically I want to return columns based on on/off flags. So if flag A is on then return the column A value, if flag B is on then return column B value.
Maybe we could say based on permissions but more fine grained.
So say you have email message with to, from, body, headers, read, read time.
So a standard user will only see to from, body, and a premium customer might be configured to also read headers, read and read time.
But would.like to do ot per column instead of group of columns.
If it was group of columns then we could easily say CASE WHEN premium THEN headers, read, read time.
Update 2
I think we can do group based "permissions" so if you are a silver member you only see some fields, but if you are gold member you see all fields.
sql-server tsql case-expression edited Feb 1 at 17:23
marc_s
580k13011181266
asked Nov 27 '18 at 15:39
user432024user432024
1,54742855
1
Having example data and example desired result would be helpful.
– MikeS
Nov 27 '18 at 15:48
I want to return columns based on on/off flags
– user432024
Nov 27 '18 at 19:19
possibly you could do this with views and column level security
– Mitch Wheat
Nov 27 '18 at 23:37
add a comment |
Say I have columns is_return_foo, is_return_bar and is_return_baz.
I need to return the foo, bar, baz columns if any of the above are respectively set to true...
Is CASE WHEN the best option?
Something like:
SELECT
CASE is_return_foo WHEN true THEN foo ELSE null
CASE is_return_bar WHEN true THEN bar ELSE null
CASE is_return_baz WHEN true THEN baz ELSE null
another_column
FROM
my_table
Update
Basically I want to return columns based on on/off flags. So if flag A is on then return the column A value, if flag B is on then return column B value.
Maybe we could say based on permissions but more fine grained.
So say you have email message with to, from, body, headers, read, read time.
So a standard user will only see to from, body, and a premium customer might be configured to also read headers, read and read time.
But would.like to do ot per column instead of group of columns.
If it was group of columns then we could easily say CASE WHEN premium THEN headers, read, read time.
Update 2
I think we can do group based "permissions" so if you are a silver member you only see some fields, but if you are gold member you see all fields.
sql-server tsql case-expression edited Feb 1 at 17:23
marc_s
580k13011181266
asked Nov 27 '18 at 15:39
user432024user432024
1,54742855
Say I have columns is_return_foo, is_return_bar and is_return_baz.
I need to return the foo, bar, baz columns if any of the above are respectively set to true...
Is CASE WHEN the best option?
Something like:
SELECT
CASE is_return_foo WHEN true THEN foo ELSE null
CASE is_return_bar WHEN true THEN bar ELSE null
CASE is_return_baz WHEN true THEN baz ELSE null
another_column
FROM
my_table
Update
Basically I want to return columns based on on/off flags. So if flag A is on then return the column A value, if flag B is on then return column B value.
Maybe we could say based on permissions but more fine grained.
So say you have email message with to, from, body, headers, read, read time.
So a standard user will only see to from, body, and a premium customer might be configured to also read headers, read and read time.
But would.like to do ot per column instead of group of columns.
If it was group of columns then we could easily say CASE WHEN premium THEN headers, read, read time.
Update 2
I think we can do group based "permissions" so if you are a silver member you only see some fields, but if you are gold member you see all fields.
sql-server tsql case-expression sql-server tsql case-expression edited Feb 1 at 17:23
marc_s
580k13011181266
asked Nov 27 '18 at 15:39
user432024user432024
1,54742855
edited Feb 1 at 17:23
marc_s
580k13011181266
asked Nov 27 '18 at 15:39
user432024user432024
1,54742855
edited Feb 1 at 17:23
marc_s
580k13011181266
edited Feb 1 at 17:23
marc_s
580k13011181266
edited Feb 1 at 17:23
marc_s
580k13011181266
580k13011181266
asked Nov 27 '18 at 15:39
user432024user432024
1,54742855
asked Nov 27 '18 at 15:39
user432024user432024
1,54742855
asked Nov 27 '18 at 15:39
user432024user432024
1,54742855
1,54742855
1
Having example data and example desired result would be helpful.
– MikeS
Nov 27 '18 at 15:48
I want to return columns based on on/off flags
– user432024
Nov 27 '18 at 19:19
possibly you could do this with views and column level security
– Mitch Wheat
Nov 27 '18 at 23:37
add a comment |
1
Having example data and example desired result would be helpful.
– MikeS
Nov 27 '18 at 15:48
I want to return columns based on on/off flags
– user432024
Nov 27 '18 at 19:19
possibly you could do this with views and column level security
– Mitch Wheat
Nov 27 '18 at 23:37
1
1
Having example data and example desired result would be helpful.
– MikeS
Nov 27 '18 at 15:48
Having example data and example desired result would be helpful.
– MikeS
Nov 27 '18 at 15:48
I want to return columns based on on/off flags
– user432024
Nov 27 '18 at 19:19
I want to return columns based on on/off flags
– user432024
Nov 27 '18 at 19:19
possibly you could do this with views and column level security
– Mitch Wheat
Nov 27 '18 at 23:37
possibly you could do this with views and column level security
– Mitch Wheat
Nov 27 '18 at 23:37
add a comment |
3 Answers
3
active
oldest
votes
Dynamic TSQL and pivot tables work for this use case.
DECLARE @Columns nvarchar(max);
DECLARE @Sql nvarchar(max);
SELECT @Columns = CONCAT('[', Column, ']') FROM Permissions
SET @Sql = '
SELECT pvt.*
FROM Data AS d
PIVOT (MIN(ColumnValue) FOR ColumnName IN (' + @Columns +')) AS pvt'
EXEC sp_executesql @Sql;
sp_executesql Reference
Pivot Reference
answered Nov 28 '18 at 0:45
rschoenbachrschoenbach
1855
Nah that's for aggregations. I just need to return columns based on users claim/permission
– user432024
Nov 28 '18 at 4:01
Pivot tables can be used for dynamic columns. If your result set only contains distinct values, aggregation will be performed on 1 row and columns will be pivoted. Can be powerful tool when used properly and with the correct domain knowledge.
– rschoenbach
Nov 28 '18 at 4:07
So basically you are converting a key/value style table to a row oriented table?
– user432024
Nov 28 '18 at 6:24
@user432024 correct. With distinct key / value pairs, you can pivot to a row without the side effects of aggregation using the above approach.
– rschoenbach
Nov 28 '18 at 14:42
add a comment |
Maybe something like this is the solution you are looking for:
SELECT
your columns here
FROM my_table
where COALESCE(is_return_foo,is_return_bar,is_return_baz) is not null
answered Nov 27 '18 at 15:49
holderholder
30017
I don't think this is what the OP is asking. Based on the way I read it, he wants this: "If Column1 = true, return Column1A" AND "If Column2 = true, return Column2A", etc. I thought COALESCE was what he was wanting to until I reread the question.
– user1011627
Nov 27 '18 at 16:30
add a comment |
The CASE statement is the only way I know of to accomplish what you are trying to do in your question...at least short of having a lot of IF/ELSE conditions that would duplicate the code for your base select statement (would make for a management nightmare). There might be better solutions available to you if we understood more about what these fields are and why this scenario exists though. My gut tells me these would be better as separate queries, but it's hard to tell based on "foo" and "bar" type examples.
answered Nov 27 '18 at 16:28
user1011627user1011627
1,2781118
Added more details...
– user432024
Nov 27 '18 at 19:28
Yeah, I thought this was what you were wanting to do. In your permissions example, personally, I would not recommend this approach to accomplish that goal. Mainly for the reason that you are moving business logic directly into your database. If you do want to do it though, the only option that I know of would be the type of thing you are currently doing with CASE unless you want the nightmare of IF/THEN logic.
– user1011627
Nov 27 '18 at 19:34
So what do you recommend then? I check the permitions in the API and return the columns based on case statement in the actual code?
– user432024
Nov 27 '18 at 20:07
It's hard to give you an "exact" answer as I don't truly know your scenario. Since I have no idea what type of technologies you are using, it also makes it difficult to give suggestions on "what" to do. In the example you gave, you only have 2 "groups": Standard and Premium. If that is all you ever expect, you could easily just create 2 distinct queries and return only the data you need from them.
– user1011627
Nov 27 '18 at 21:04
If you want this to get more complicated over time, you will need additional schema to support that and this example of an on/off flag would likely become very limiting fairly quickly....ie...can 2 groups have it on and 1 off, etc? You could also just return all the columns you need to satisfy your business requirement (ie...showing all the data) and then have 2 class based objects that represent your 2 groups and only serialize data from db to the fields that pertain to that object type.
– user1011627
Nov 27 '18 at 21:04
|
show 3 more comments
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%2f53503129%2fhow-to-return-columns-conditionally-in-sql-server-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Dynamic TSQL and pivot tables work for this use case.
DECLARE @Columns nvarchar(max);
DECLARE @Sql nvarchar(max);
SELECT @Columns = CONCAT('[', Column, ']') FROM Permissions
SET @Sql = '
SELECT pvt.*
FROM Data AS d
PIVOT (MIN(ColumnValue) FOR ColumnName IN (' + @Columns +')) AS pvt'
EXEC sp_executesql @Sql;
sp_executesql Reference
Pivot Reference
answered Nov 28 '18 at 0:45
rschoenbachrschoenbach
1855
Nah that's for aggregations. I just need to return columns based on users claim/permission
– user432024
Nov 28 '18 at 4:01
Pivot tables can be used for dynamic columns. If your result set only contains distinct values, aggregation will be performed on 1 row and columns will be pivoted. Can be powerful tool when used properly and with the correct domain knowledge.
– rschoenbach
Nov 28 '18 at 4:07
So basically you are converting a key/value style table to a row oriented table?
– user432024
Nov 28 '18 at 6:24
@user432024 correct. With distinct key / value pairs, you can pivot to a row without the side effects of aggregation using the above approach.
– rschoenbach
Nov 28 '18 at 14:42
add a comment |
Dynamic TSQL and pivot tables work for this use case.
DECLARE @Columns nvarchar(max);
DECLARE @Sql nvarchar(max);
SELECT @Columns = CONCAT('[', Column, ']') FROM Permissions
SET @Sql = '
SELECT pvt.*
FROM Data AS d
PIVOT (MIN(ColumnValue) FOR ColumnName IN (' + @Columns +')) AS pvt'
EXEC sp_executesql @Sql;
sp_executesql Reference
Pivot Reference
answered Nov 28 '18 at 0:45
rschoenbachrschoenbach
1855
Nah that's for aggregations. I just need to return columns based on users claim/permission
– user432024
Nov 28 '18 at 4:01
Pivot tables can be used for dynamic columns. If your result set only contains distinct values, aggregation will be performed on 1 row and columns will be pivoted. Can be powerful tool when used properly and with the correct domain knowledge.
– rschoenbach
Nov 28 '18 at 4:07
So basically you are converting a key/value style table to a row oriented table?
– user432024
Nov 28 '18 at 6:24
@user432024 correct. With distinct key / value pairs, you can pivot to a row without the side effects of aggregation using the above approach.
– rschoenbach
Nov 28 '18 at 14:42
add a comment |
Dynamic TSQL and pivot tables work for this use case.
DECLARE @Columns nvarchar(max);
DECLARE @Sql nvarchar(max);
SELECT @Columns = CONCAT('[', Column, ']') FROM Permissions
SET @Sql = '
SELECT pvt.*
FROM Data AS d
PIVOT (MIN(ColumnValue) FOR ColumnName IN (' + @Columns +')) AS pvt'
EXEC sp_executesql @Sql;
sp_executesql Reference
Pivot Reference
answered Nov 28 '18 at 0:45
rschoenbachrschoenbach
1855
Dynamic TSQL and pivot tables work for this use case.
DECLARE @Columns nvarchar(max);
DECLARE @Sql nvarchar(max);
SELECT @Columns = CONCAT('[', Column, ']') FROM Permissions
SET @Sql = '
SELECT pvt.*
FROM Data AS d
PIVOT (MIN(ColumnValue) FOR ColumnName IN (' + @Columns +')) AS pvt'
EXEC sp_executesql @Sql;
sp_executesql Reference
Pivot Reference
answered Nov 28 '18 at 0:45
rschoenbachrschoenbach
1855
edited Nov 28 '18 at 0:50
answered Nov 28 '18 at 0:45
rschoenbachrschoenbach
1855
answered Nov 28 '18 at 0:45
rschoenbachrschoenbach
1855
answered Nov 28 '18 at 0:45
rschoenbachrschoenbach
1855
1855
Nah that's for aggregations. I just need to return columns based on users claim/permission
– user432024
Nov 28 '18 at 4:01
Pivot tables can be used for dynamic columns. If your result set only contains distinct values, aggregation will be performed on 1 row and columns will be pivoted. Can be powerful tool when used properly and with the correct domain knowledge.
– rschoenbach
Nov 28 '18 at 4:07
So basically you are converting a key/value style table to a row oriented table?
– user432024
Nov 28 '18 at 6:24
@user432024 correct. With distinct key / value pairs, you can pivot to a row without the side effects of aggregation using the above approach.
– rschoenbach
Nov 28 '18 at 14:42
add a comment |
Nah that's for aggregations. I just need to return columns based on users claim/permission
– user432024
Nov 28 '18 at 4:01
Pivot tables can be used for dynamic columns. If your result set only contains distinct values, aggregation will be performed on 1 row and columns will be pivoted. Can be powerful tool when used properly and with the correct domain knowledge.
– rschoenbach
Nov 28 '18 at 4:07
So basically you are converting a key/value style table to a row oriented table?
– user432024
Nov 28 '18 at 6:24
@user432024 correct. With distinct key / value pairs, you can pivot to a row without the side effects of aggregation using the above approach.
– rschoenbach
Nov 28 '18 at 14:42
Nah that's for aggregations. I just need to return columns based on users claim/permission
– user432024
Nov 28 '18 at 4:01
Nah that's for aggregations. I just need to return columns based on users claim/permission
– user432024
Nov 28 '18 at 4:01
Pivot tables can be used for dynamic columns. If your result set only contains distinct values, aggregation will be performed on 1 row and columns will be pivoted. Can be powerful tool when used properly and with the correct domain knowledge.
– rschoenbach
Nov 28 '18 at 4:07
Pivot tables can be used for dynamic columns. If your result set only contains distinct values, aggregation will be performed on 1 row and columns will be pivoted. Can be powerful tool when used properly and with the correct domain knowledge.
– rschoenbach
Nov 28 '18 at 4:07
So basically you are converting a key/value style table to a row oriented table?
– user432024
Nov 28 '18 at 6:24
So basically you are converting a key/value style table to a row oriented table?
– user432024
Nov 28 '18 at 6:24
@user432024 correct. With distinct key / value pairs, you can pivot to a row without the side effects of aggregation using the above approach.
– rschoenbach
Nov 28 '18 at 14:42
@user432024 correct. With distinct key / value pairs, you can pivot to a row without the side effects of aggregation using the above approach.
– rschoenbach
Nov 28 '18 at 14:42
add a comment |
Maybe something like this is the solution you are looking for:
SELECT
your columns here
FROM my_table
where COALESCE(is_return_foo,is_return_bar,is_return_baz) is not null
answered Nov 27 '18 at 15:49
holderholder
30017
I don't think this is what the OP is asking. Based on the way I read it, he wants this: "If Column1 = true, return Column1A" AND "If Column2 = true, return Column2A", etc. I thought COALESCE was what he was wanting to until I reread the question.
– user1011627
Nov 27 '18 at 16:30
add a comment |
Maybe something like this is the solution you are looking for:
SELECT
your columns here
FROM my_table
where COALESCE(is_return_foo,is_return_bar,is_return_baz) is not null
answered Nov 27 '18 at 15:49
holderholder
30017
I don't think this is what the OP is asking. Based on the way I read it, he wants this: "If Column1 = true, return Column1A" AND "If Column2 = true, return Column2A", etc. I thought COALESCE was what he was wanting to until I reread the question.
– user1011627
Nov 27 '18 at 16:30
add a comment |
Maybe something like this is the solution you are looking for:
SELECT
your columns here
FROM my_table
where COALESCE(is_return_foo,is_return_bar,is_return_baz) is not null
answered Nov 27 '18 at 15:49
holderholder
30017
Maybe something like this is the solution you are looking for:
SELECT
your columns here
FROM my_table
where COALESCE(is_return_foo,is_return_bar,is_return_baz) is not null
answered Nov 27 '18 at 15:49
holderholder
30017
answered Nov 27 '18 at 15:49
holderholder
30017
answered Nov 27 '18 at 15:49
holderholder
30017
answered Nov 27 '18 at 15:49
holderholder
30017
30017
I don't think this is what the OP is asking. Based on the way I read it, he wants this: "If Column1 = true, return Column1A" AND "If Column2 = true, return Column2A", etc. I thought COALESCE was what he was wanting to until I reread the question.
– user1011627
Nov 27 '18 at 16:30
add a comment |
I don't think this is what the OP is asking. Based on the way I read it, he wants this: "If Column1 = true, return Column1A" AND "If Column2 = true, return Column2A", etc. I thought COALESCE was what he was wanting to until I reread the question.
– user1011627
Nov 27 '18 at 16:30
I don't think this is what the OP is asking. Based on the way I read it, he wants this: "If Column1 = true, return Column1A" AND "If Column2 = true, return Column2A", etc. I thought COALESCE was what he was wanting to until I reread the question.
– user1011627
Nov 27 '18 at 16:30
I don't think this is what the OP is asking. Based on the way I read it, he wants this: "If Column1 = true, return Column1A" AND "If Column2 = true, return Column2A", etc. I thought COALESCE was what he was wanting to until I reread the question.
– user1011627
Nov 27 '18 at 16:30
add a comment |
The CASE statement is the only way I know of to accomplish what you are trying to do in your question...at least short of having a lot of IF/ELSE conditions that would duplicate the code for your base select statement (would make for a management nightmare). There might be better solutions available to you if we understood more about what these fields are and why this scenario exists though. My gut tells me these would be better as separate queries, but it's hard to tell based on "foo" and "bar" type examples.
answered Nov 27 '18 at 16:28
user1011627user1011627
1,2781118
Added more details...
– user432024
Nov 27 '18 at 19:28
Yeah, I thought this was what you were wanting to do. In your permissions example, personally, I would not recommend this approach to accomplish that goal. Mainly for the reason that you are moving business logic directly into your database. If you do want to do it though, the only option that I know of would be the type of thing you are currently doing with CASE unless you want the nightmare of IF/THEN logic.
– user1011627
Nov 27 '18 at 19:34
So what do you recommend then? I check the permitions in the API and return the columns based on case statement in the actual code?
– user432024
Nov 27 '18 at 20:07
It's hard to give you an "exact" answer as I don't truly know your scenario. Since I have no idea what type of technologies you are using, it also makes it difficult to give suggestions on "what" to do. In the example you gave, you only have 2 "groups": Standard and Premium. If that is all you ever expect, you could easily just create 2 distinct queries and return only the data you need from them.
– user1011627
Nov 27 '18 at 21:04
If you want this to get more complicated over time, you will need additional schema to support that and this example of an on/off flag would likely become very limiting fairly quickly....ie...can 2 groups have it on and 1 off, etc? You could also just return all the columns you need to satisfy your business requirement (ie...showing all the data) and then have 2 class based objects that represent your 2 groups and only serialize data from db to the fields that pertain to that object type.
– user1011627
Nov 27 '18 at 21:04
|
show 3 more comments
The CASE statement is the only way I know of to accomplish what you are trying to do in your question...at least short of having a lot of IF/ELSE conditions that would duplicate the code for your base select statement (would make for a management nightmare). There might be better solutions available to you if we understood more about what these fields are and why this scenario exists though. My gut tells me these would be better as separate queries, but it's hard to tell based on "foo" and "bar" type examples.
answered Nov 27 '18 at 16:28
user1011627user1011627
1,2781118
Added more details...
– user432024
Nov 27 '18 at 19:28
Yeah, I thought this was what you were wanting to do. In your permissions example, personally, I would not recommend this approach to accomplish that goal. Mainly for the reason that you are moving business logic directly into your database. If you do want to do it though, the only option that I know of would be the type of thing you are currently doing with CASE unless you want the nightmare of IF/THEN logic.
– user1011627
Nov 27 '18 at 19:34
So what do you recommend then? I check the permitions in the API and return the columns based on case statement in the actual code?
– user432024
Nov 27 '18 at 20:07
It's hard to give you an "exact" answer as I don't truly know your scenario. Since I have no idea what type of technologies you are using, it also makes it difficult to give suggestions on "what" to do. In the example you gave, you only have 2 "groups": Standard and Premium. If that is all you ever expect, you could easily just create 2 distinct queries and return only the data you need from them.
– user1011627
Nov 27 '18 at 21:04
If you want this to get more complicated over time, you will need additional schema to support that and this example of an on/off flag would likely become very limiting fairly quickly....ie...can 2 groups have it on and 1 off, etc? You could also just return all the columns you need to satisfy your business requirement (ie...showing all the data) and then have 2 class based objects that represent your 2 groups and only serialize data from db to the fields that pertain to that object type.
– user1011627
Nov 27 '18 at 21:04
|
show 3 more comments
The CASE statement is the only way I know of to accomplish what you are trying to do in your question...at least short of having a lot of IF/ELSE conditions that would duplicate the code for your base select statement (would make for a management nightmare). There might be better solutions available to you if we understood more about what these fields are and why this scenario exists though. My gut tells me these would be better as separate queries, but it's hard to tell based on "foo" and "bar" type examples.
answered Nov 27 '18 at 16:28
user1011627user1011627
1,2781118
The CASE statement is the only way I know of to accomplish what you are trying to do in your question...at least short of having a lot of IF/ELSE conditions that would duplicate the code for your base select statement (would make for a management nightmare). There might be better solutions available to you if we understood more about what these fields are and why this scenario exists though. My gut tells me these would be better as separate queries, but it's hard to tell based on "foo" and "bar" type examples.
answered Nov 27 '18 at 16:28
user1011627user1011627
1,2781118
answered Nov 27 '18 at 16:28
user1011627user1011627
1,2781118
answered Nov 27 '18 at 16:28
user1011627user1011627
1,2781118
answered Nov 27 '18 at 16:28
user1011627user1011627
1,2781118
1,2781118
Added more details...
– user432024
Nov 27 '18 at 19:28
Yeah, I thought this was what you were wanting to do. In your permissions example, personally, I would not recommend this approach to accomplish that goal. Mainly for the reason that you are moving business logic directly into your database. If you do want to do it though, the only option that I know of would be the type of thing you are currently doing with CASE unless you want the nightmare of IF/THEN logic.
– user1011627
Nov 27 '18 at 19:34
So what do you recommend then? I check the permitions in the API and return the columns based on case statement in the actual code?
– user432024
Nov 27 '18 at 20:07
It's hard to give you an "exact" answer as I don't truly know your scenario. Since I have no idea what type of technologies you are using, it also makes it difficult to give suggestions on "what" to do. In the example you gave, you only have 2 "groups": Standard and Premium. If that is all you ever expect, you could easily just create 2 distinct queries and return only the data you need from them.
– user1011627
Nov 27 '18 at 21:04
If you want this to get more complicated over time, you will need additional schema to support that and this example of an on/off flag would likely become very limiting fairly quickly....ie...can 2 groups have it on and 1 off, etc? You could also just return all the columns you need to satisfy your business requirement (ie...showing all the data) and then have 2 class based objects that represent your 2 groups and only serialize data from db to the fields that pertain to that object type.
– user1011627
Nov 27 '18 at 21:04
|
show 3 more comments
Added more details...
– user432024
Nov 27 '18 at 19:28
Yeah, I thought this was what you were wanting to do. In your permissions example, personally, I would not recommend this approach to accomplish that goal. Mainly for the reason that you are moving business logic directly into your database. If you do want to do it though, the only option that I know of would be the type of thing you are currently doing with CASE unless you want the nightmare of IF/THEN logic.
– user1011627
Nov 27 '18 at 19:34
So what do you recommend then? I check the permitions in the API and return the columns based on case statement in the actual code?
– user432024
Nov 27 '18 at 20:07
It's hard to give you an "exact" answer as I don't truly know your scenario. Since I have no idea what type of technologies you are using, it also makes it difficult to give suggestions on "what" to do. In the example you gave, you only have 2 "groups": Standard and Premium. If that is all you ever expect, you could easily just create 2 distinct queries and return only the data you need from them.
– user1011627
Nov 27 '18 at 21:04
If you want this to get more complicated over time, you will need additional schema to support that and this example of an on/off flag would likely become very limiting fairly quickly....ie...can 2 groups have it on and 1 off, etc? You could also just return all the columns you need to satisfy your business requirement (ie...showing all the data) and then have 2 class based objects that represent your 2 groups and only serialize data from db to the fields that pertain to that object type.
– user1011627
Nov 27 '18 at 21:04
Added more details...
– user432024
Nov 27 '18 at 19:28
Added more details...
– user432024
Nov 27 '18 at 19:28
Yeah, I thought this was what you were wanting to do. In your permissions example, personally, I would not recommend this approach to accomplish that goal. Mainly for the reason that you are moving business logic directly into your database. If you do want to do it though, the only option that I know of would be the type of thing you are currently doing with CASE unless you want the nightmare of IF/THEN logic.
– user1011627
Nov 27 '18 at 19:34
Yeah, I thought this was what you were wanting to do. In your permissions example, personally, I would not recommend this approach to accomplish that goal. Mainly for the reason that you are moving business logic directly into your database. If you do want to do it though, the only option that I know of would be the type of thing you are currently doing with CASE unless you want the nightmare of IF/THEN logic.
– user1011627
Nov 27 '18 at 19:34
So what do you recommend then? I check the permitions in the API and return the columns based on case statement in the actual code?
– user432024
Nov 27 '18 at 20:07
So what do you recommend then? I check the permitions in the API and return the columns based on case statement in the actual code?
– user432024
Nov 27 '18 at 20:07
It's hard to give you an "exact" answer as I don't truly know your scenario. Since I have no idea what type of technologies you are using, it also makes it difficult to give suggestions on "what" to do. In the example you gave, you only have 2 "groups": Standard and Premium. If that is all you ever expect, you could easily just create 2 distinct queries and return only the data you need from them.
– user1011627
Nov 27 '18 at 21:04
It's hard to give you an "exact" answer as I don't truly know your scenario. Since I have no idea what type of technologies you are using, it also makes it difficult to give suggestions on "what" to do. In the example you gave, you only have 2 "groups": Standard and Premium. If that is all you ever expect, you could easily just create 2 distinct queries and return only the data you need from them.
– user1011627
Nov 27 '18 at 21:04
If you want this to get more complicated over time, you will need additional schema to support that and this example of an on/off flag would likely become very limiting fairly quickly....ie...can 2 groups have it on and 1 off, etc? You could also just return all the columns you need to satisfy your business requirement (ie...showing all the data) and then have 2 class based objects that represent your 2 groups and only serialize data from db to the fields that pertain to that object type.
– user1011627
Nov 27 '18 at 21:04
If you want this to get more complicated over time, you will need additional schema to support that and this example of an on/off flag would likely become very limiting fairly quickly....ie...can 2 groups have it on and 1 off, etc? You could also just return all the columns you need to satisfy your business requirement (ie...showing all the data) and then have 2 class based objects that represent your 2 groups and only serialize data from db to the fields that pertain to that object type.
– user1011627
Nov 27 '18 at 21:04
|
show 3 more comments
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%2f53503129%2fhow-to-return-columns-conditionally-in-sql-server-query%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
1
Having example data and example desired result would be helpful.
– MikeS
Nov 27 '18 at 15:48
I want to return columns based on on/off flags
– user432024
Nov 27 '18 at 19:19
possibly you could do this with views and column level security
– Mitch Wheat
Nov 27 '18 at 23:37