CODEWITHSUNDEEP
sqlsql-server-2014
rottenbanana
rottenbanana

Reputation: 209

SQL Server Case Statements

I am trying to write a query that would only generate one row per employerid instead of generating multiple rows per employer and only filling one of the columns at a time. I am getting the below with my query:

enter image description here

The Query that I am using is this:

SELECT Employer.employerid,
    CASE WHEN Service.xxserviceid = '1' THEN 'Number 1' ELSE 'NULL' END AS Service1,
    CASE WHEN Service.xxserviceid  = '2' THEN 'Number 2' ELSE 'NULL' END AS Service2,
    CASE WHEN Service.xxserviceid  = '3' THEN 'Number 2' ELSE 'NULL' END AS Service3
FROM Employer
INNER JOIN Service
ON Service.employerid = Employer.employerid;

So i want the Columns Service1, Service2, Service3 to be filled in one line per employer rather than multiple lines per employer.

Upvotes: 1

Views: 33

Answers (3)

Cristian Lupascu
Cristian Lupascu

Reputation: 40516

You can use GROUP BY + MAX to reduce the results to one line per employee:

SELECT Employer.employerid,
  max(CASE WHEN Service.xxserviceid = '1' THEN 'Number 1' ELSE 'NULL' END) AS Service1,
  max(CASE WHEN Service.xxserviceid  = '2' THEN 'Number 2' ELSE 'NULL' END) AS Service2,
  max(CASE WHEN Service.xxserviceid  = '3' THEN 'Number 3' ELSE 'NULL' END) AS Service3
FROM Employer
INNER JOIN Service
  ON Service.employerid = Employer.employerid
GROUP BY Employer.employerid;

If you think about it, though, you don't even need the join with the Employer table, because all you use from it is the employerid column, which is already present in Service.

So, the query can be reduced to:

SELECT employerid,
  max(CASE WHEN xxserviceid = '1' THEN 'Number 1' ELSE 'NULL' END) AS Service1,
  max(CASE WHEN xxserviceid  = '2' THEN 'Number 2' ELSE 'NULL' END) AS Service2,
  max(CASE WHEN xxserviceid  = '3' THEN 'Number 3' ELSE 'NULL' END) AS Service3
FROM Service
GROUP BY employerid;

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

Upvotes: 1

istovatis
istovatis

Reputation: 1408

As I understand your question, you may need just one Service column. Other answers provided in this post should work. If you need just one Service column though, you can try the following:

SELECT Employer.employerid,
    'Number ' + Max(Service.xxserviceid) AS Service1
FROM Employer
INNER JOIN Service
ON Service.employerid = Employer.employerid;
GROUP BY Employer.employerid

Upvotes: 0

Doolius
Doolius

Reputation: 874

SELECT Employer.employerid,
    MAX(CASE WHEN Service.xxserviceid = '1' THEN 'Number 1' ELSE 'NULL' END) AS Service1,
    MAX(CASE WHEN Service.xxserviceid  = '2' THEN 'Number 2' ELSE 'NULL' END) AS Service2,
    MAX(CASE WHEN Service.xxserviceid  = '3' THEN 'Number 2' ELSE 'NULL' END) AS Service3
FROM Employer
INNER JOIN Service
ON Service.employerid = Employer.employerid;
GROUP BY Employer.employerID

Upvotes: 0

Related Questions