Building a SELECT clause with dynamic number of columns

Question

Is it possible to create a SELECT clause with a varying number of columns to be returned depending on joined tables?

For instance. If I join a table depending on a value in the WHERE-clause I want to return either tbl1.col1, tbl1.col2 if tabl tbl1 is joined or tbl2.col4, tbl2.col5, tbl2.col8 if table tbl2 is joined.

Is this possible? How?

You can create views for each different scenario, but can you give an example of what you mean? — lc.
...you seem to want two different statements here, why not just write the queries separately. If there is a common portion, perhaps you want a view. Otherwise, you have to build the statement with dynamic SQL, how is up to the frameworks/application-layer language. — Clockwork-Muse
My question is related to my other question stackoverflow.com/questions/24184078 — du-it

Thorsten Kettner Thorsten Kettner · Accepted Answer · 2014-06-13T06:41:32

No, you can't write one query that sometimes returns n columns and another time m columns. What you can do is something like this: Use UNION ALL on two queries with conditions that either query 1 or query 2 Returns data. Have columns match, so where one query has no value let it select null in this place.

select tbl1.col1 as firstname, tbl1.col2 as lastname, null as street, tbl1.col3 as job as street from ...
where @variable = 1
UNION ALL
select tbl2.col4 as firstname, tbl2.col5 as lastname, tbl2.col8 as street, null as job from ...
where @variable = 2;

Or you just build your SQL dynamically with whatever language and use completely different SQL, which is what one would normally do.

Building a SELECT clause with dynamic number of columns

1 Answers