select if(

Question

Post reply

select if(

hermandm

SSC Rookie

Points: 31
More actions
February 15, 2010 at 4:30 am

#215725

I have a table with three columns
Name - Class - Subclass
Tom - Animal - Cat
Jerry - Animal - Mouse
Baloo - Animal - Bear
I need to convert this data structure to something more general like
Name - Animals - Cats - Mice - Bears
Tom - X - Tom - 0 - 0
Jerry - X - 0 - Jerry - 0
Baloo - X - 0 - 0 - Baloo
I think I need something like SELECT IF, but can't get this to work. Any suggestions? Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

One way of setting the values of the columns is using the CASE function. Have a look in BOL (Books On Line).

BrainDonor

Steve Hall
Linkedin
Blog Site

apat SSCertifiable Points: 5929 More actions · Answer 2

apat

SSCertifiable

Points: 5929

February 15, 2010 at 2:26 pm

#1118962

try using pivot table. That should help.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Herman,

You're new so just a tip for you... Both of the answers above are technically correct but if you want a coded answer, take a look at the article at the first link in my signature line below. It'll only take you 5 minutes once you get the jist of it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

apat SSCertifiable Points: 5929 More actions · Answer 4

Herman,

Try this...

create table #test (Name varchar(100), Class varchar(100), Subclass varchar(100))

insert into #test (Name, Class, SubClass) values ('Tom','Animal','Cat')

insert into #test (Name, Class, SubClass)values ('Jerry','Animal','Mouse')

insert into #test (Name, Class, SubClass)values ('Ballo','Animal','Bear')

-- select * from #test

select Name, Animal, Cat, Mouse, Bear

from (

select Name, 'X' as Animal, Class, SubClass

from #test) up

pivot (count(Subclass) for Subclass in (Cat, Mouse, Bear)) as pvt

order by Name

apat SSCertifiable Points: 5929 More actions · Answer 5

use this.....

create table #test (Name varchar(100), Class varchar(100), Subclass varchar(100))

insert into #test (Name, Class, SubClass) values ('Tom','Animal','Cat')

insert into #test (Name, Class, SubClass)values ('Jerry','Animal','Mouse')

insert into #test (Name, Class, SubClass)values ('Ballo','Animal','Bear')

insert into #test (Name, Class, SubClass)values ('Pat','Animal','Dog')

insert into #test (Name, Class, SubClass)values ('Mat','Animal','Bear')

insert into #test (Name, Class, SubClass)values ('Sam','Animal','Cat')

--------------------------------------------------------------------------------------

with mycte (Name, Animal, Cat, Mouse, Bear, Dog)

as

(select Name, Animal, Cat, Mouse, Bear, Dog

from (

select Name, 'X' as Animal, Class, SubClass

from #test) up

pivot (count(Subclass) for Subclass in (Cat, Mouse, Bear, Dog)) as pvt

)

select Name, Animal, CASE when Bear > 0 then c.Name else '0' end as Bear,

CASE when Mouse > 0 then c.Name else '0' end as Mouse,

CASE when Cat > 0 then c.Name else '0' end as Cat,

CASE when Dog > 0 then c.Name else '0' end as Dog

from mycte c

order by Name

yvr238 Valued Member Points: 64 More actions · Answer 6

Try this :

select distinct [name]

,animal = isnull(case when class = 'animal' then 'X' end,0)

,cats = isnull(case when subclass = 'cat' then [name] end,0)

,mouse = isnull(case when subclass = 'mouse' then [name] end,0)

,bear = isnull(case when subclass = 'bear' then [name] end,0)

from #test