Case Statement in SQL, How to Return Multiple Variables

case statement in SQL, how to return multiple variables?

The basic way, unfortunately, is to repeat yourself.

SELECT
  CASE WHEN <condition 1> THEN <a1> WHEN <condition 2> THEN <a2> ELSE <a3> END,
  CASE WHEN <condition 1> THEN <b1> WHEN <condition 2> THEN <b2> ELSE <b3> END
FROM 
  <table> 

Fortunately, most RDBMS are clever enough to NOT have to evaluate the conditions multiple times. It's just redundant typing.

In MS SQL Server (2005+) you could possible use CROSS APPLY as an alternative to this. Though I have no idea how performant it is...

SELECT
  *
FROM
  <table>
CROSS APPLY
  (
   SELECT a1, b1 WHERE <condition 1>
   UNION ALL
   SELECT a2, b2 WHERE <condition 2>
   UNION ALL
   SELECT a3, b3 WHERE <condition 3>
  )
  AS case_proxy

The noticable downside here is that there is no ELSE equivalent and as all the conditions could all return values, they need to be framed such that only one can ever be true at a time.

EDIT

If Yuck's answer is changed to a UNION rather than JOIN approach, it becomes very similar to this. The main difference, however, being that this only scans the input data set once, rather than once per condition (100 times in your case).

EDIT

I've also noticed that you may mean that the values returned by the CASE statements are fixed. All records that match the same condition get the exact sames values in value1 and value2. This could be formed like this...

WITH
  checked_data AS
(
  SELECT
    CASE WHEN <condition1> THEN 1
         WHEN <condition2> THEN 2
         WHEN <condition3> THEN 3
         ...
         ELSE                   100
    END AS condition_id,
    *
  FROM
    <table>
)
,
  results (condition_id, value1, value2) AS
(
   SELECT 1, a1, b1
   UNION ALL
   SELECT 2, a2, b2
   UNION ALL
   SELECT 3, a3, b3
   UNION ALL
   ...
   SELECT 100, a100, b100
)
SELECT
  *
FROM
  checked_data
INNER JOIN
  results
    ON results.condition_id = checked_data.condition_id

Return multiple values for 'Then' clause in an SQL case expression

You can try below - use UNION

DEMO

    select * from
    (
    select a.fileid,contolnames,file1 as files
    from Files a inner join sampletable b on a.fileid=b.fileid
    union
    select a.fileid,contolnames,file2 as files 
    from Files a inner join sampletable b on  a.fileid=b.fileid
  )A order by fileid

OUTPUT:

fileid  contolnames files
1         abc   abc.pdf
1         abc   abcdef.pdf
2         ghf   ghf.pdf
2         ghf   ghfjkl.pdf
3         hjy   hjyui.pdf
3         hjy   hjy.pdf

Case when to return multiple values

You need to concatenate the values:

select id,
       trim(',' from
            (case when V1 = 1 then 'A,' end) ||
             case when V2 = 1 then 'B,' end) ||
             case when V3 = 1 then 'C,' end) ||
             case when V4 = 1 then 'D,' end) ||
             case when V5 = 1 then v5_text
            )
           )

How to Return Multiple Values from CASE clause in Where Condition

You should try following,

Declare @status nvarchar(50) = 'XXXX'
select 
    * 
from cardimport
where
1 = case when isnull(@status,'') = '' then 1 
    else
        case when status = @status then 1 else 0 end
    end 

It will give you all the rows when status is null and when status is not null then give you only matching data.

Returning multiple values in a CASE expression

How about just using two CASE expressions?

CASE
  WHEN (org.org_misc_data = 'PAC') THEN 'pac|' ELSE '' END
  + CASE 
    WHEN (org.dues_category = 'PART') THEN 'partner_member'
    WHEN (org.dues_category = 'FREE' AND org.org_status_flag = 'P') THEN 'associate_member'
    ELSE 'non_member'
END AS org_status

It's tough to give a definitive answer because you haven't really outlined all of the possible permutations.

SQL CASE returning two values

A case statement can only return one value. You can easily write what you want as:

SELECT (CASE WHEN codeVersion = 'A' THEN ACode
             ELSE BCode
        END) as Code, Title
FROM Code.CodeRef
WHERE @useCode in (ACode, BCode);

SQL return multiple values from CASE statement

 SELECT * FROM Table WHERE
    (COLUMN_NAME_2 = 'X' AND COLUMN_NAME_1 = 'A') OR
    (COLUMN_NAME_2 = 'Y' AND COLUMN_NAME_1 IN ('B', 'C'))

or

 SELECT * FROM Table WHERE COLUMN_NAME_2 = 'X' AND COLUMN_NAME_1 = 'A'
 UNION ALL
 SELECT * FROM Table WHERE COLUMN_NAME_2 = 'Y' AND COLUMN_NAME_1 IN ('B', 'C')

This presumes that you only want results with X or Y in COLUMN_NAME_2. If you want other rows it's not possible to tell which ones from your original SQL.

CASE statement still returning multiple rows

In your group by statement, you are specifying course_level. Remove course_level from your group by statement and it should consolidate the rows.

---

Related Topics