How to avoid a column from select query conditionally

Question

I want to select columns from a table conditionally,I Just followed this link,

SELECT ID,(CASE WHEN @check=0
          THEN Teacher END) 'Teacher'
FROM Register

Result when @check =1

---------------------
|   ID   | Teacher  |
---------------------
|   10   |  NULL    |
|   20   |  NULL    |
--------------------

In this case I don't want to select Teacher Column,How to do this?

If it is not possible How to delete column which has null value for all row ?

AS I know this is not possible with column select statement, however you can delete column after select statement — Sandip - Frontend Developer, Dec 29 '16 at 06:50
@SandipPatel How to delete column which has only null value for all rows — mass, Dec 29 '16 at 06:51
You cant delete the column, just create a dynamic query. https://msdn.microsoft.com/en-us/library/ms188001.aspx — Juan Carlos Oropeza, Dec 29 '16 at 06:52
Possible duplicate of [How to detect and remove a column that contains only null values?](http://stackoverflow.com/questions/1665868/how-to-detect-and-remove-a-column-that-contains-only-null-values) — ahmed abdelqader, Dec 29 '16 at 06:58
is @check =0 means 0 index column?? i.e ID?? what if it is 2?? — Shakeer Mirza, Dec 29 '16 at 07:00

Gopakumar N.Kurup · Answer 1 · 2016-12-29T06:57:39.500

2

Use a IF ELSE condition

IF @check = 0
BEGIN
    SELECT ID, Teacher 'Teacher'
    FROM Register
END
ELSE
BEGIN
    SELECT ID FROM Register
END

OR use a dynamic query like below

DECLARE @qryStr VARCHAR(2000)
DECLARE @check INT =0
SET @qryStr = 'SELECT ID '+CASE WHEN @check=0 THEN ',Teacher' ELSE '' END +' FROM Register'
PRINT @qryStr
EXEC(@qryStr)

edited Dec 29 '16 at 06:57

answered Dec 29 '16 at 06:53

Gopakumar N.Kurup

916
4
18

:) This is possible but This is not I want. It is not possible in my case since there are multiple columns, to describe the question I chose simple example with one column – mass Dec 29 '16 at 06:55
I have just modified the answer. Please check whether that will help – Gopakumar N.Kurup Dec 29 '16 at 06:58
In my case I have multiple columns with different condition – mass Dec 29 '16 at 06:59
Just share your requirement properly. – Gopakumar N.Kurup Dec 29 '16 at 07:02

score 0 · Answer 2 · answered Dec 29 '16 at 08:42

This dynamical qry is not good idea. You should rewrite your solution or denormalize your tables.

Variant 1:

declare @removeCol1 bit = 1
declare @removeCol3 bit = 1

declare @qry varchar(4000) = 'select col1, col2, col3, col4, col5 from dbo.yourtable'

if @removeCol1 = 1 set @qry = replace(replace(@qry, 'col1,', ''), 'col1', '')
if @removeCol3 = 1 set @qry = replace(replace(@qry, 'col3,', ''), 'col3', '')

print @qry
exec (@qry)

Variant 2: Use pivot on your table and filter properly. But your example isn't enought for example query.

How to avoid a column from select query conditionally

2 Answers2