SQL Server Case Statements -

i trying write query generate 1 row per employerid instead of generating multiple rows per employer , filling 1 of columns @ time. getting below query:

the query using this:

select employer.employerid,     case when service.xxserviceid = '1' 'number 1' else 'null' end service1,     case when service.xxserviceid  = '2' 'number 2' else 'null' end service2,     case when service.xxserviceid  = '3' 'number 2' else 'null' end service3 employer inner join service on service.employerid = employer.employerid; 

so want columns service1, service2, service3 filled in 1 line per employer rather multiple lines per employer.

you can use group by + max reduce results 1 line per employee:

select employer.employerid,   max(case when service.xxserviceid = '1' 'number 1' else 'null' end) service1,   max(case when service.xxserviceid  = '2' 'number 2' else 'null' end) service2,   max(case when service.xxserviceid  = '3' 'number 3' else 'null' end) service3 employer inner join service   on service.employerid = employer.employerid group employer.employerid; 

if think it, though, don't need join employer table, because use employerid column, present in service.

so, query can reduced to:

select employerid,   max(case when xxserviceid = '1' 'number 1' else 'null' end) service1,   max(case when xxserviceid  = '2' 'number 2' else 'null' end) service2,   max(case when xxserviceid  = '3' 'number 3' else 'null' end) service3 service group employerid; 

sqlfiddle: http://www.sqlfiddle.com/#!6/004a9/2