CODEWITHSUNDEEP
sqlsql-servercase
Becky Purland
Becky Purland

Reputation: 89

How to write a case statement with multiple whens that bring out more than one row

I have a case statement which I am using to create a column from several columns, depending on the answer in each column. I need to show a new row for each time the answer is 1 in any of the columns, but at the moment it is only showing one row for the first one that matches.

Please see the code and examples. Thanks

I am using SQL Server 17 I am querying from tables I have no access rights to, so cannot create tables etc. I am looking to query and not declare and set values. 

      ID,
      CASE
          WHEN ORG1 = 1 THEN 'ORG1'
          WHEN ORG2 = 1 THEN 'ORG2'
          WHEN ORG3 = 1 THEN 'ORG3'
          WHEN ORG4 = 1 THEN 'ORG4'
          WHEN ORG5 = 1 THEN 'ORG5'
      else 'None' end as ORG

FROM TBL

current table looks like:

ID  ORG1   ORG2   ORG3   ORG4   ORG5
1    1      0      0       0     1
2    0      1      1       0     0
3    0      0      1       0     1
4    0      1      0       0     0
5    0      0      0       1     0
6    1      0      0       1     0

What I want it the outcome to look like is:

ID   ORG
1    ORG1
1    ORG5
2    ORG2    
2    ORG3
3    ORG3
3    ORG5
4    ORG2
5    ORG4
6    ORG1
6    ORG4

What I am getting with my code is:

ID   ORG
1    ORG1
2    ORG2   
3    ORG3
4    ORG2
5    ORG4
6    ORG1

Upvotes: 0

Views: 233

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I would use cross apply. However, you need additional logic for the 'NONE' value -- even though your sample data doesn't include any examples:

SELECT t.ID, v.ORG
FROM TBL t CROSS APPLY
     (VALUES ('ORG1', ORG1),
             ('ORG2', ORG2),
             ('ORG3', ORG3),
             ('ORG4', ORG4),
             ('ORG5', ORG5)
     ) V(ORG, val),
WHERE val = 1
UNION ALL
SELECT t.ID, 'None'
FROM TBL
WHERE ORG1 = 0 AND ORG2 = 0 AND ORG3 = 0 AND ORG4 = 0 AND ORG5 = 0;

Or without the UNION ALL:

SELECT t.ID, v.ORG
FROM TBL t CROSS APPLY
     (VALUES ('ORG1', ORG1),
             ('ORG2', ORG2),
             ('ORG3', ORG3),
             ('ORG4', ORG4),
             ('ORG5', ORG5),
             ('NONE', (CASE WHEN ORG1 = 0 AND ORG2 = 0 AND ORG3 = 0 AND ORG4 = 0 AND ORG5 = 0 THEN 1 ELSE 0 END))
     ) V(ORG, val),
WHERE val = 1

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

you can use this.

-- Sample Data
DECLARE @TBL TABLE (ID  INT, ORG1 INT,  ORG2 INT,  ORG3  INT, ORG4 INT,  ORG5 INT)
INSERT INTO @TBL VALUES
(1, 1, 0, 0, 0, 1),
(2, 0, 1, 1, 0, 0),
(3, 0, 0, 1, 0, 1),
(4, 0, 1, 0, 0, 0),
(5, 0, 0, 0, 1, 0),
(6, 1, 0, 0, 1, 0),
(7, 0, 0, 0, 0, 0)


SELECT ID, UNPVT.ORG FROM @TBL
UNPIVOT( VAL FOR ORG IN ([ORG1], [ORG2], [ORG3], [ORG4], [ORG5])) UNPVT
WHERE VAL = 1

Result:

ID          ORG
----------- ------------
1           ORG1
1           ORG5
2           ORG2
2           ORG3
3           ORG3
3           ORG5
4           ORG2
5           ORG4
6           ORG1
6           ORG4

If you have cases which all columns are "0"

SELECT ID, UNPVT.ORG FROM 
    (SELECT *, 
        ([ORG1] + [ORG2] + [ORG3] + [ORG4] + [ORG5])^1 AS [None]
     FROM @TBL) SRC
UNPIVOT( VAL FOR ORG IN ([ORG1], [ORG2], [ORG3], [ORG4], [ORG5], [None])) UNPVT
WHERE VAL = 1

Result:

ID          ORG
----------- ------------
1           ORG1
1           ORG5
2           ORG2
2           ORG3
3           ORG3
3           ORG5
4           ORG2
5           ORG4
6           ORG1
6           ORG4
7           None

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

do unpivot after using case expression

select id,org from (
select id, case when org1=1 then 'org1' end as og1,
case when org2=1 then 'org2' end as og2,
case when org3=1 then 'org3' end as og3,
case when org4=1 then 'org4' end as og4,
case when org5=1 then 'org5' end as og5

 from table_name
 ) a unpivot (org for value in (og1,og2,og3,og4,og5)) p

id  org
1   org1
1   org5
2   org2
2   org3
3   org3
3   org5
4   org2
5   org4
6   org1
6   org4

here demo

Upvotes: 0

Ted
Ted

Reputation: 4067

A simple approach would be a union all like so:

select * from
(
  select id, 'ORG1' as org from tbl where org1=1
  union all
  select id, 'ORG2' as org from tbl where org1=2
  union all
  select id, 'ORG3' as org from tbl where org1=3
  union all
  select id, 'ORG4' as org from tbl where org1=4
  union all
  select id, 'ORG5' as org from tbl where org1=5
) as tmp

I am guessing that you want to filter out all 0 columns. If not just delete the where clause

Upvotes: 0

asmgx
asmgx

Reputation: 8004

this is another approach 

declare @Tbl as table (id int, org1 int, org2 int, org3 int, org4 int, org5 int)

insert into @Tbl values 
(1,1,0,0,0,1),
(2,0,1,1,0,0),
(3,0,0,1,0,1),
(4,0,1,0,0,0),
(5,0,0,0,1,0),
(6,1,0,0,1,0)

select * from @Tbl

declare @temp as table (org varchar(5))
insert into @temp values
('org1'),('org2'),('org3'),('org4'),('org5')

select * from @temp


select * from @Tbl cross join @temp
where (org='org1' and org1=1) or
(org='org2' and org2=1) or
(org='org3' and org3=1) or
(org='org4' and org4=1) or
(org='org5' and org5=1)
order by id

Upvotes: 0

Related Questions