Return multiple columns (int datatype) inside a CASE expression

Question

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.

Answer 1

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;

Answer 2

If you do not want to return anything (i.e. if you want to omit the row entirely) when the condition is not met, then just do not use CASE, use your condition in the WHERE clause:

WITH
  ...
SELECT
  a.id AS a_id,
  b.id AS b_id
FROM
  ...
WHERE
  ...
  AND (b.id - a.id) > 1
;

If, however, you want to return something (like nulls) in both columns instead and would like to avoid repeating the condition, then you could use OUTER APPLY, but you would probably need to rewrite your comma join to use the explicit join syntax, like this:

WITH
  ...
SELECT
  x.a_id,
  x.b_id
FROM
  cte AS a
  INNER JOIN cte AS b ON b.Rownumber = a.Rownumber + 1
  OUTER APPLY
  (
    SELECT
      a.id AS a_id,
      b.id AS b_id,
    WHERE
      (b.id - a.id) > 1
  ) AS x
;

Alternatively, you could replace the inner join with an outer join in this manner:

WITH
  ...
SELECT
  CASE WHEN b.id IS NOT NULL THEN a.id END AS a_id,
  b.id AS b_id
FROM
  cte AS a
  LEFT JOIN cte AS b ON b.Rownumber = a.Rownumber + 1 AND (b.id - a.id) > 1
;

Note that if you are using SQL Server 2012 or later version, you can avoid the self-join entirely by using the LEAD function:

WITH cte AS
  (
    SELECT
      id = id,
      next_id = LEAD(id) OVER (ORDER BY id ASC
                               ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    FROM
      dbo.Test
  )
SELECT
  id,
  next_id
FROM
  cte
WHERE
  next_id - id > 1
;