CODEWITHSUNDEEP
sqlsql-servert-sql
AllWorkNoPlay
AllWorkNoPlay

Reputation: 454

Sql return multiple rows for records with multiple flags set

Dear smart developers out there,

given following data set:

Name    Football  Tennis  Snowboarding
--------------------------------------
Jane    true      false   false
Jack    true      true    false
Sue     false     true    true

I would like to obtain the following output from a SQL query:

Name    Sport
-------------
Jane    Football
Jack    Football
Jack    Tennis
Sue     Tennis
Sue     Snowboarding

Any idea how this can be done? (I need this to ease the creation of a SQL Report)

Thanks in advance, AllWorkNoPlay

Upvotes: 1

Views: 578

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can do this in various ways. Here is a method with cross apply:

select v.name, v.sport
from table t cross apply
     (values (t.name, 'football', t.football), 
             (t.name, 'tennis', t.tennis), 
             (t.name, 'Snowboarding', t.Snowboarding)
     ) v(name, sport, flg)
where flg = 'true';

Upvotes: 4

dnoeth
dnoeth

Reputation: 60462

This is usually done using UNIONs:

select Name, 'Football' as Sport
from tab
where Football = 'true'
UNION ALL
select Name, 'Tennis' as Sport
from tab
where Tennis = 'true'
UNION ALL
select Name, 'Snowboarding' as Sport
from tab
where Snowboarding = 'true'

Of course in a normalized data model this would be much easier :)

Upvotes: 1

Related Questions