sql replace cell value with column name and avoid null values

Question

Hi i have two tables Agents, Skills and target query as below

Agents

-------------------------------------
PF       |Name       |HireDate       |
---------|-----------|---------------|
54       |Jack       |01-Jan-2012    |
55       |Aline      |01-Jan-2012    |
-------------------------------------

Skills

-------------------------------------------------------------
PF       |Writing    |Swimming       |KickOff    |Shopping   |
---------|-----------|---------------|-----------------------
54       |Null       |01-Dec-2012    |Null       |Null       |
55       |01-Mar-2012|01-FeB-2012    |Null       |15-Nov-2012|
-------------------------------------------------------------

I want query to select PF, Hiredate from Agents table and avaliable skills from skills table, The date on skills tables means that agents learned this skill on this data, but if there is null value that means agents didn't have this skill, but i want to replace date with column name The below table explane what is my query

MyQuery

------------------------------------------------------
PF       |HireDate       |Avalivble skills            |
---------|---------------|----------------------------|
54       |01-Jan-2012    |Swimming                    |
55       |01-Jan-2012    |Writing, Swimming, Shopping |
------------------------------------------------------

What RDBMS you are using? SQL Server? MySQL? Oracle? DB2? etc... — John Woo
– John Woo, Commented Jan 31, 2013 at 14:13

Andomar · Accepted Answer · 2013-01-31 14:56:36Z

1

select  PF
,       HireDate
,       replace(
            iif(Writing is null;'';'Writing, ') +
            iif(Swimming is null;'';'Swimming, ') +
            iif(KickOff is null;'';'KickOff, ') +
            iif(Shopping is null;'';'Shopping, ') + '$';
            ', $'; '')
from    Agents a
join    Skills s
on      s.PF = a.PF

edited Jan 31, 2013 at 14:56

answered Jan 31, 2013 at 13:54

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

KaZzA Over a year ago

Thanks but its return error "Syntax error (missing operator)"

openshac Over a year ago

You'll need to trim off the trailing ', '

Andomar Over a year ago

@openshac: I guess you could... not always important though.

Andomar Over a year ago

Depending on your locale, try to relace the ; with , ?

Fionnuala Over a year ago

Tested in Euro locale MS Access:

select  a.PF ,       HireDate ,       replace(             iif(Writing is null,'','Writing, ') +             iif(Swimming is null,'','Swimming, ') +             iif(KickOff is null,'','KickOff, ') +             iif(Shopping is null,'','Shopping, ') + '$',             ', $', '') from    Agents a inner join    Skills s on      s.PF = a.PF

Collectives™ on Stack Overflow

sql replace cell value with column name and avoid null values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related