SQL Server – Return Multiple Columns Inside CASE Expression

casesql server

How can I get multiple columns from a CASE expression? 

with cte
as
(
  select
    ROW_NUMBER() over (order by id)'Rownumber',
    id
  from
    dbo.Test
)
select 
  case 
    when (b.id-a.id) > 1 then a.id
    else ' '
  end 
from
  cte a,
  cte b
where
  b.Rownumber = a.Rownumber+1

In the above query, I want to select a.id,b.id only if (b.id-a.id) > 1.
I tried to do it using a nested CASE expression as well as by UNION, but still not able to get the expected result.

Best Answer

You need to move that to the where portion of your query, otherwise it won't eliminate anything. Take a look at the below. I've also changed the query to use the proper join syntax which you should adopt. 

WITH    cte
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY id ) AS Rownumber
                      , id
               FROM     dbo.Test
             )
    SELECT  a.id AS A_ID, b.id AS B_ID
    FROM    cte a
            INNER JOIN cte b ON b.Rownumber = ( a.Rownumber + 1 )
    WHERE   ( b.id - a.id ) > 1;
Related Question