title: SQL Case When - delete 'empty' rows from output
body: <p>I'm having difficulties with a query that I'm writing. The query looks like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This gives the following output:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I want the output to be only one row, like this:</p>
  
  <pre><code>2010  2011  2012  2013  2014  2015
  ----------------------------------
   1     0     3     2     2     0
  </code></pre>
  
  <p>I found a way to do it, but it's a real ugly and slow (relatively speaking) query:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>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.</p>
  
  <p>---------------------EDIT------------------</p>
  
  <p>Jarlh gave a good solution, the following query gives the right output:</p>
  
  <pre><code>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
  </code></pre>