March 11, 2014 at 11:44 am
We have the following query that pulls employee information along with dependents they have associated to them. the query runs with no issues but the example employeeid has 3 dependents but they are listed as 3 seperate rows under this employee id. What we are trying to do is list just 1 row for this employeeid with all the dependent information all on that same row.
SELECT
e.FirstName AS EEFirstName,
e.LastName AS EELastName,
e.EmployeeId AS EEID,
ae.OptionId AS MedicalOptionID,
o.ShortDesc AS MedicalOptionName,
ae.TierId AS MedicalTierID,
t.ShortDesc AS MedicalTierName,
d.firstname AS DependentFirstName,
d.LastName AS DependentLastName,
d.Relationship AS DependentRelationship
FROM vw_employee e
JOIN util_active_elections ae ON e.PersonId = ae.PersonId
JOIN pr_option_def o ON ae.BenId = o.BenId AND ae.OptionId = o.OptionId AND ae.PlanYear = o.PlanYear
JOIN pr_tier_def t ON ae.BenId = t.BenId AND ae.OptionId = t.OptionId AND ae.TierId = t.TierId AND ae.PlanYear = t.PlanYear
LEFT OUTER JOIN vw_dependent d ON e.personid = d.personid
WHERE ae.PlanYear = 2012
AND ae.RecordStatus = 'A'
AND e.StatusCode = 'A'
AND ae.BenId = '1'
AND e.EmployeeId = '014010'
ORDER BY e.employeeid
March 11, 2014 at 12:13 pm
You need to work with cross tabs or pivot. I suggest that you do it before joining the dependents view to your query.
To know how to handle cross tabs, read the following article: http://www.sqlservercentral.com/articles/T-SQL/63681/
Before you say that you aren't aggregating data, let me tell you that you can work perfectly with char data using MAX (or even MIN). Here's an untested example on how you might do it. I hope that you're using variables instead of hardcoded values or even creating the code concatenating the string instead of using a stored procedure.
WITH DependentRows AS(
SELECT d.personid,
d.firstname,
d.lastname,
d.relationship,
ROW_NUMBER() OVER(PARTITION BY d.personid ORDER BY d.dependentid /*or any column that will defin order*/) rn
FROM vw_employee e
LEFT OUTER JOIN vw_dependent d ON e.personid = d.personid
WHERE e.EmployeeId = '014010'
),
DependentSingleRow AS(
SELECT personid,
MAX( CASE WHEN rn = 1 THEN firstname ELSE '' END) firstname1,
MAX( CASE WHEN rn = 1 THEN lastname ELSE '' END) lastname1,
MAX( CASE WHEN rn = 1 THEN relationship ELSE '' END) relationship1,
MAX( CASE WHEN rn = 2 THEN firstname ELSE '' END) firstname2,
MAX( CASE WHEN rn = 2 THEN lastname ELSE '' END) lastname2,
MAX( CASE WHEN rn = 2 THEN relationship ELSE '' END) relationship2,
MAX( CASE WHEN rn = 3 THEN firstname ELSE '' END) firstname3,
MAX( CASE WHEN rn = 3 THEN lastname ELSE '' END) lastname3,
MAX( CASE WHEN rn = 3 THEN relationship ELSE '' END) relationship3
FROM DependentRows
GROUP BY personid
)
SELECT
e.FirstName AS EEFirstName,
e.LastName AS EELastName,
e.EmployeeId AS EEID,
ae.OptionId AS MedicalOptionID,
o.ShortDesc AS MedicalOptionName,
ae.TierId AS MedicalTierID,
t.ShortDesc AS MedicalTierName,
d.firstname AS DependentFirstName,
d.LastName AS DependentLastName,
d.Relationship AS DependentRelationship
FROM vw_employee e
JOIN util_active_elections ae ON e.PersonId = ae.PersonId
JOIN pr_option_def o ON ae.BenId = o.BenId AND ae.OptionId = o.OptionId AND ae.PlanYear = o.PlanYear
JOIN pr_tier_def t ON ae.BenId = t.BenId AND ae.OptionId = t.OptionId AND ae.TierId = t.TierId AND ae.PlanYear = t.PlanYear
LEFT OUTER JOIN DependentSingleRow d ON e.personid = d.personid
WHERE ae.PlanYear = 2012
AND ae.RecordStatus = 'A'
AND e.StatusCode = 'A'
AND ae.BenId = '1'
AND e.EmployeeId = '014010'
ORDER BY e.employeeid
March 11, 2014 at 12:45 pm
This seems fairly complex. Couldnt this be completed by a Union query?
March 11, 2014 at 2:19 pm
tstagliano (3/11/2014)
This seems fairly complex. Couldnt this be completed by a Union query?
A union requires that each query have the number of columns. You are wanting to have derived columns based on the data. If you did this with a union you would have to have NULL for the columns outside the current set and still do a MAX on the columns so you would get the data into a single row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2014 at 2:21 pm
Do you have a situation where there might be more than 3 dependents? This sounds a lot like you probably don't know the number of columns for output because it is based on the data found in the table. You can read more about the crosstab approach that Luis posted by following the link in my signature.
If the number of columns is unknown you will need to do a dynamic cross tab here. That is a little more complicated but the article in my signature for dynamic cross tabs will walk you through this in great detail.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply