Getiing multiple rows as output using case statement in sql

user_14534083 0
Viewed 170

I have a table,

id category column1 column2 column3
1   1        val1a   val1b   val1c   
2   2        val2a   val2b   val2c
3   3        val3a   val3b   val3c

from which I need to select columns based on multiple conditions something like below.

SELECT id, category, column1, column2, column3
            FROM table
            WHERE id = @id
                AND category IN (
                select case when (@input1='Yes' AND @input2='Yes') then (select category from table where category in ('1','2'))
                     when (@input1='Yes' AND @input2='No') then (select category from table where category ='1')
                     when (@input1='No' AND @input2='Yes') then (select category from table where category ='2')
                     else ''
                     end as category)
            END

Input values @input1 and @input2 are grabbed from another table, need to select and output rows with category in ('1','2') according to above condition.

What I need -

if input1=Yes and input2=Yes output rows with category in ('1','2')

if input1=Yes and input2=No output rows with category in ('1')

if input1=No and input2=Yes output rows with category in ('2')

if input1=No and input2=No output rows with category in ('')

Does case statement output multiple values? Need help.

case asp.net-mvc tsql sql-server sql
4 Answers

check out the code below, you don't need to use CASE WHEN, just use where condition combinations will do the trick.

create table [Test] ( id int, category nvarchar(10));

insert [Test] values(1,'1');
insert [Test] values(2,'2');
insert [Test] values(3,'1');
insert [Test] values(4,'2');


declare @input1 varchar(10) = 'Yes'
declare @input2 varchar(10) = 'No'

SELECT id, category
FROM [Test]
WHERE 
    ( 
        ( @input1 = 'Yes' and  category ='1')
        or 
        ( @input2 = 'Yes' and  category ='2')
    )
user_4207110

If you wanted to use in, you could use:

where id = @id and
      category in ( (case when @input1 = 'Yes' then 1 end),
                    (case when @input2 = 'Yes' then 2 end)
                  )

The default value for the case is NULL and that will fail any comparison.

Also, note that I removed the single quotes around '1' and '2'. These look like numbers, so I assume that category is a number. Quotes should only be used around string and date constants.

user_11440350

Maybe this:

SELECT id, category, column1, column2, column3
FROM table
WHERE id = @id
AND category IN 
(
    select category 
    from table 
    where 
    (
        category = '1'
        AND
        @input1='Yes'
    )
    OR
    (
        category = '2'
        AND
        @input1='Yes'
    )
)

or this:

SELECT id, category, column1, column2, column3
FROM table
WHERE id = @id
AND
(
    (
        @input1='Yes' AND @input2='Yes' AND category IN  (select category from table where category in ('1','2')
    )
    OR
    (
        @input1='Yes' AND @input2='No' AND category IN (select category from table where category ='1')
    )
    OR
    (
        @input1='No' AND @input2='Yes' AND category IN (select category from table where category ='2')
    )
)
user_10803540

I would suggest you to form a mapping table and leverage it in the SELECT query. It will result in simplified code and more clear approach.

;WITH CTE_MappingTable AS
(
SELECT *
FROM
(
values
('Yes','Yes',1),
('Yes','Yes',2),
('Yes','No',1),
('No','Yes',2)
) as t(input1,input2,category)
)
SELECT id, category, column1, column2, column3
            FROM table as t
            INNER JOIN CTE_MappingTable  as c
            ON c.Category = t.Category 
            WHERE id = @id AND c.input1 = @input1 AND c.input2 = @input2
user_6349350
Related