CODEWITHSUNDEEP
sqlsql-server
Ashesh Das
Ashesh Das

Reputation: 365

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

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:

enter image description here

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?

Upvotes: 4

Views: 3021

Answers (3)

Sean
Sean

Reputation: 1474

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)

Upvotes: 3

Ullas
Ullas

Reputation: 11566

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)

Upvotes: 3

New
New

Reputation: 671

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.

Upvotes: 0

Related Questions