How to return columns conditionally in SQL Server query?

Question

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.

Having example data and example desired result would be helpful. — MikeS, Nov 27 '18 at 15:48
possibly you could do this with views and column level security — Mitch Wheat, Nov 27 '18 at 23:37

score 1 · Answer 1 · answered Nov 27 '18 at 15:49

1

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

holder

585
2
9

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

rschoenbach · Answer 2 · 2018-11-28T00:50:28.353

1

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

edited Nov 28 '18 at 00:50

answered Nov 28 '18 at 00:45

rschoenbach

496
4
6

Nah that's for aggregations. I just need to return columns based on users claim/permission – user432024 Nov 28 '18 at 04: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 04:07
So basically you are converting a key/value style table to a row oriented table? – user432024 Nov 28 '18 at 06: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

score 0 · Answer 3 · answered Nov 27 '18 at 16:28

0

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

user1011627

1,741
1
17
25

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
Check out AutoMapper (AM) (and projection in general) if you have never used it...AM has a great extension method called ProjectTo that allows you to build a POCO and have a LINQ query built to only get the data you want. That would be a great solution to this problem that eliminates most of the negatives that you will run into with these other approaches. – user1011627 Nov 27 '18 at 21:05
http://docs.automapper.org/en/stable/Projection.html http://docs.automapper.org/en/stable/Queryable-Extensions.html – user1011627 Nov 27 '18 at 21:05
Ok bassically if you are a silver member you see specific fields and if you are gold You see all. – user432024 Nov 27 '18 at 23:32

How to return columns conditionally in SQL Server query?

3 Answers3