CODEWITHSUNDEEP
sqlrowsql-server-2014case-when
TheNiers
TheNiers

Reputation: 237

SQL Case When - delete 'empty' rows from output

I'm having difficulties with a query that I'm writing. The query looks like this:

SELECT case when Year(LOAN_START_DATE) = 2010 then 
            max(LOAN_RES_BANK_CODE) else 0 end as '2010',
       case when Year(LOAN_START_DATE) = 2011 then
            max(LOAN_RES_BANK_CODE) else 0 end as '2011',
       case when Year(LOAN_START_DATE) = 2012 then 
            max(LOAN_RES_BANK_CODE) else 0 end as '2012',
       case when Year(LOAN_START_DATE) = 2013 then 
            max(LOAN_RES_BANK_CODE) else 0 end as '2013',
       case when Year(LOAN_START_DATE) = 2014 then 
            max(LOAN_RES_BANK_CODE) else 0 end as '2014',
       case when Year(LOAN_START_DATE) = 2015 then 
            max(LOAN_RES_BANK_CODE) else 0 end as '2015'
from LLOAN l
inner join LACMSTR c on c.ACCT_NUMBER = l.LOAN_ACCT_1 and c.SourceID = l.SourceID
where l.SourceID = 1
and c.ACCT_NUMBER = 1065
group by LOAN_START_DATE

This gives the following output:

2010  2011  2012  2013  2014  2015
----------------------------------
 0     0     0     0     0     0
 0     0     0     0     0     0
 0     0     0     0     0     0
 0     0     0     0     0     0
 0     0     0     0     0     0
 0     0     0     0     0     0
 0     0     0     0     0     0
 1     0     0     0     0     0
 0     0     3     0     0     0
 0     0     0     2     0     0
 0     0     0     2     0     0
 0     0     0     0     2     0

I want the output to be only one row, like this:

2010  2011  2012  2013  2014  2015
----------------------------------
 1     0     3     2     2     0

I found a way to do it, but it's a real ugly and slow (relatively speaking) query:

select (select ISNULL(max(LOAN_RES_BANK_CODE),0)
             from PfeDs.dbo.LLOAN t1, pfeds.dbo.LACMSTR t2
             where t1.SourceID = t2.SourceID
             and t2.SourceID = 1
             and ACCT_NUMBER = LOAN_ACCT_1
             and ACCT_NUMBER = 1065
             and year(LOAN_START_DATE) = 2010) as '2010',
             (select ISNULL(max(LOAN_RES_BANK_CODE),0)
             from PfeDs.dbo.LLOAN t1, pfeds.dbo.LACMSTR t2
             where t1.SourceID = t2.SourceID
             and t2.SourceID = 1
             and ACCT_NUMBER = LOAN_ACCT_1
             and ACCT_NUMBER = 1065
             and year(LOAN_START_DATE) = 2011) as '2011',
             (select ISNULL(max(LOAN_RES_BANK_CODE),0)
             from PfeDs.dbo.LLOAN t1, pfeds.dbo.LACMSTR t2
             where t1.SourceID = t2.SourceID
             and t2.SourceID = 1
             and ACCT_NUMBER = LOAN_ACCT_1
             and ACCT_NUMBER = 1065
             and year(LOAN_START_DATE) = 2012) as '2012',
             (select ISNULL(max(LOAN_RES_BANK_CODE),0)
             from PfeDs.dbo.LLOAN t1, pfeds.dbo.LACMSTR t2
             where t1.SourceID = t2.SourceID
             and t2.SourceID = 1
             and ACCT_NUMBER = LOAN_ACCT_1
             and ACCT_NUMBER = 1065
             and year(LOAN_START_DATE) = 2013) as '2013',
             (select ISNULL(max(LOAN_RES_BANK_CODE),0)
             from PfeDs.dbo.LLOAN t1, pfeds.dbo.LACMSTR t2
             where t1.SourceID = t2.SourceID
             and t2.SourceID = 1
             and ACCT_NUMBER = LOAN_ACCT_1
             and ACCT_NUMBER = 1065
             and year(LOAN_START_DATE) = 2014) as '2014',
             (select ISNULL(max(LOAN_RES_BANK_CODE),0)
             from PfeDs.dbo.LLOAN t1, pfeds.dbo.LACMSTR t2
             where t1.SourceID = t2.SourceID
             and t2.SourceID = 1
             and ACCT_NUMBER = LOAN_ACCT_1
             and ACCT_NUMBER = 1065
             and year(LOAN_START_DATE) = 2015) as '2015'

SQL is not my strong point. In what way should I change my original query to get the result that I'm looking for. I'm using SQL Server 2014.

---------------------EDIT------------------

Jarlh gave a good solution, the following query gives the right output:

select max(J10) as '2010', max(J11) as '2011', max(J12) '2012', max(J13) as '2013', max(J14) as '2014', max(J15) as '2015'
from (
SELECT case when Year(LOAN_START_DATE) = 2010 then max(LOAN_RES_BANK_CODE) else 0 end as J10,
case when Year(LOAN_START_DATE) = 2011 then max(LOAN_RES_BANK_CODE) else 0 end as J11,
case when Year(LOAN_START_DATE) = 2012 then max(LOAN_RES_BANK_CODE) else 0 end as J12,
case when Year(LOAN_START_DATE) = 2013 then max(LOAN_RES_BANK_CODE) else 0 end as J13,
case when Year(LOAN_START_DATE) = 2014 then max(LOAN_RES_BANK_CODE) else 0 end as J14,
case when Year(LOAN_START_DATE) = 2015 then max(LOAN_RES_BANK_CODE) else 0 end as J15
from pfeds.dbo.LLOAN l
inner join PfeDs.dbo.LACMSTR c on c.ACCT_NUMBER = l.LOAN_ACCT_1 and c.SourceID = l.SourceID
where l.SourceID = 1
and c.ACCT_NUMBER = 1065
group by LOAN_START_DATE) as test

Upvotes: 2

Views: 2122

Answers (2)

Xavier
Xavier

Reputation: 467

If you want to hide lines where all values are 0, why don't you try to add one of these in the where clause ?

and max(LOAN_RES_BANK_CODE) > 0

or 

and LOAN_RES_BANK_CODE >0

Upvotes: 1

jarlh
jarlh

Reputation: 44786

Use the original query as a derived table, then MAX the columns:

select MAX('2010'), ... 
from (
SELECT case when Year(LOAN_START_DATE) = 2010 then 
            max(LOAN_RES_BANK_CODE) else 0 end as '2010',
...
)

Upvotes: 2

Related Questions