SQL Server Merge Multiple Rows
-
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!
-
Looks like this works:
SELECT EmployeeID, MIN(CoverageCode1) AS CC1
, MIN(CoverageDate1) AS CD1
, MIN(CoverageCode2) AS CC2
, MIN(CoverageDate2) AS CD2
FROM vwCoverage
GROUP BY EmployeeID;
-
What if the query returns
1 CID 01/01/2015 NULL NULL
1 CID 01/02/2015 NULL NULL
1 CID 01/03/2015 AH 01/25/2015
1 CIE 01/01/2015 NULL NULL
?
-
The query will either return 1 record for each Employee ID where the Coverage Code 2 and Coverage Date 2 are NULL or it will return a maximum of 2 records for each Employee ID as in the example where Coverage Code 2 and Coverage Date 2 are NULL in one record and populated in the second.
-
OK. Can that two rows contain 3 distinct codes and dates, i.e.
1 CID 01/03/2015 AH 01/25/2015
1 CIE 01/01/2015 NULL NULL
?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply