How to ignore a column in select statement if it has null vales

Question

I have the following query:

SELECT WC_WARD_CATEGORY,
   MAX(CASE WHEN months = 'JAN' THEN BOR END) JAN,
   MAX(CASE WHEN months = 'FEB' THEN BOR END) FEB,
   MAX(CASE WHEN months = 'MAR' THEN BOR END) MAR,
   MAX(CASE WHEN months = 'APR' THEN BOR END) APR,
   MAX(CASE WHEN months = 'MAY' THEN BOR END) MAY,
   MAX(CASE WHEN months = 'JUN' THEN BOR END) JUN,
   MAX(CASE WHEN months = 'JUL' THEN BOR END) JUL,
   MAX(CASE WHEN months = 'AUG' THEN BOR END) AUG,
   MAX(CASE WHEN months = 'SEP' THEN BOR END) SEP,
   MAX(CASE WHEN months = 'OCT' THEN BOR END) OCT,
   MAX(CASE WHEN months = 'NOV' THEN BOR END) NOV,
   MAX(CASE WHEN months = 'DEC' THEN BOR END) DEC
FROM bor2
GROUP BY WC_WARD_CATEGORY

It is giving me the following output:

All I want is the NULL values to go away and show me only the columns which have proper data. So I want OCT,NOV and DEC to be present in the output but not the other columns. Is it possible using SQL?

I don't this is possible with *static* sql. You have to use dynamic sql with prepared statements in MySQL. — Giorgos Betsos, Feb 25 '16 at 11:30
In SQL Server also you should use Dynamic SQL => Possible related question [SQL exclude a column using SELECT * except columnA FROM tableA?](http://stackoverflow.com/q/729197/4519059) ;). — shA.t, Feb 25 '16 at 11:32
And you would have to have the full query results before you could remove any of the columns, so this would not be too effective to achieve in sql. I would rather remove the columns containing only null values in the fron-end. — Shadow, Feb 25 '16 at 11:36
I am trying to form a JSON from the query and thus invariably NULL values are getting populated in my JSON string, so it does not help!! — Ashesh Das, Feb 25 '16 at 11:40
Can you add the wanted result as well? (I can't figure out what you are asking for...) — jarlh, Feb 25 '16 at 11:58

Ullas · Answer 1 · 2016-02-25T11:59:04.720

If sql-server then just copy the result set what you are getting to a temp table and then try the following query.

Use dynamic sql.
Compare the total count of rows with total rows having null.
If both the counts are same then exclude that particular column else include.

Query

SELECT WC_WARD_CATEGORY,
   MAX(CASE WHEN months = 'JAN' THEN BOR END) JAN,
   MAX(CASE WHEN months = 'FEB' THEN BOR END) FEB,
   MAX(CASE WHEN months = 'MAR' THEN BOR END) MAR,
   MAX(CASE WHEN months = 'APR' THEN BOR END) APR,
   MAX(CASE WHEN months = 'MAY' THEN BOR END) MAY,
   MAX(CASE WHEN months = 'JUN' THEN BOR END) JUN,
   MAX(CASE WHEN months = 'JUL' THEN BOR END) JUL,
   MAX(CASE WHEN months = 'AUG' THEN BOR END) AUG,
   MAX(CASE WHEN months = 'SEP' THEN BOR END) SEP,
   MAX(CASE WHEN months = 'OCT' THEN BOR END) OCT,
   MAX(CASE WHEN months = 'NOV' THEN BOR END) NOV,
   MAX(CASE WHEN months = 'DEC' THEN BOR END) DEC 
INTO #temp
FROM bor2 
GROUP BY   WC_WARD_CATEGORY;

Then,

declare @strsql varchar(max)
  set @strsql = 'select '
  set @strsql += 
  (select case when  (select COUNT(*) from #temp where JAN is null ) 
  <> (select count(*) from #temp ) then 'JAN, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where FEB is null) 
  <> (select count(*) from #temp ) then 'FEB, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where MAR is null) 
  <> (select count(*) from #temp ) then 'MAR, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where APR is null) 
  <> (select count(*) from #temp ) then 'APR, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where MAY is null) 
  <> (select count(*) from #temp ) then 'MAY, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where JUN is null) 
  <> (select count(*) from #temp ) then 'JUN, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where JUL is null) 
  <> (select count(*) from #temp ) then 'JUL, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where AUG is null) 
  <> (select count(*) from #temp ) then 'AUG, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where SEP is null) 
  <> (select count(*) from #temp ) then 'SEP, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where OCT is null) 
  <> (select count(*) from #temp ) then 'OCT, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where NOV is null) 
  <> (select count(*) from #temp ) then 'NOV, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #temp  where DEC is null) 
  <> (select count(*) from #temp ) then 'DEC, ' else '' end)
  set @strsql = LEFT(@strsql,len(@strsql) -1)
  set @strsql += ' from #temp'

  exec (@strsql)

This gives me the proper result, but I like the solution of Sean Coetzee more which is given below. It is taken less amount of coding. Nevertheless this solution is also perfect, so thank you for helping me out :) — Ashesh Das, Feb 25 '16 at 12:59

Sean · Accepted Answer · 2016-02-25T12:09:34.557

Also using dynamic SQL, but with a pivot table:

declare @pc varchar(max)

select @pc = isnull(@pc + ',', '') + mnths
  from (
select distinct '[' + months + ']' mnths, convert(date, '1 ' + months + ' 1') ord
  from (select months
          from bor2
         group by months, WC_WARD_CATEGORY 
        having max(bor) is not null) as a ) as b order by ord

declare @sql varchar(max)
select @sql = '
select *
 from 
(
select WC_WARD_CATEGORY, months, bor
  from bor2
) as SourceTable 
PIVOT
(
max(bor) 
for months in (' + @pc + ')
) as PivotTable;'

execute(@sql)

score 0 · Answer 3 · edited Feb 25 '16 at 11:56

0

Please check whether this solves your issue

SELECT * FROM WC_WARD_CATEGORY FOR XML PATH('NotNull')

This will give you an XML file where only not null columns are present for each row, then depending on your need, you can apply XSLT or script task to get the result from this input as tabular format.

edited Feb 25 '16 at 11:56

Alireza

4,976
1
23
36

answered Feb 25 '16 at 11:39

New

675
1
11
21

WC_WARD_CATEGORY is not my table name. On top of that I am not very sure how to use the file that will be created to generate the JSON – Ashesh Das Feb 25 '16 at 11:47

How to ignore a column in select statement if it has null vales

3 Answers3