Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Server Merge Multiple Rows

Author  Topic 

jkbeaumont1
Starting Member

1 Post

Posted - 2014-11-17 : 19:53:15
I have the following problem and it seems simple but I am stuck.

I have resulting rows from a query similar to the following:



The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:

SELECT
[Employee ID] = emp.employee_id,
[Coverage Code 1] = enr.coverage_code,
[Coverage Date 1] = enr.coverage_date,
[Coverage Code 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_code else NULL end,
[Coverage Date 2] = case when enr.product_type = 'Accident.Accident'
then enr.coverage_date else NULL end
FROM
employees emp
join enrollment enr
on emp.employer_id = enr.employer_id
WHERE
emp.company_id = 'A&P' and
enr.product_type in ('Info.General', 'Accident.Accident') and
enr.enroll_status = 'closed'

I basically want to merge the like Employee ID's together into a single row like the following:



I know I have done this before and it is probably pretty simple but I am experiencing a brain fart. Can someone give me a quick assist?

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-18 : 09:09:33
something like this:


SELECT emp.employee_id AS [Employee ID]
, enr.coverage_code AS [Coverage Code 1]
, enr.coverage_date AS [Coverage Date 1]
, MAX(CASE WHEN enr.product_type = 'Accident.Accident' THEN enr.coverage_code END) AS [Coverage Code 2]
, MAX(CASE WHEN enr.product_type = 'Accident.Accident' THEN enr.coverage_date END) AS [Coverage Date 2]

FROM employees emp
join enrollment enr
on emp.employer_id = enr.employer_id
WHERE
emp.company_id = 'A&P' and
enr.product_type in ('Info.General', 'Accident.Accident') and
enr.enroll_status = 'closed'

GROUP BY emp.employee_id
, enr.coverage_code
, enr.coverage_date
Go to Top of Page
   

- Advertisement -