SQL Server Merge Multiple Rows

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources