August 22, 2014 at 7:10 am
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits. Thoughts? the attached screenshot is the result set for this query.
SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,
hcd.Address1,
hcd.Address2,
hcd.City,
hcd.State,
hcd.PostalCode,
ae1.BenId
FROM v_health_census_data hcd
JOIN util_active_elections ae1 ON hcd.PlanYear = ae1.PlanYear AND hcd.PersonId = ae1.PersonId
WHERE GroupId > 0 AND hcd.PersonId = 1104
August 22, 2014 at 12:55 pm
This isn't an intuitive thing to do in T-SQL and I normally suggest putting it in a reporting tool, like SSRS that will group for you. Having said that, here's an article that shows several ways to do this, https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 22, 2014 at 1:02 pm
If you want one column for each benefit, check the following articles about Cross Tabs and Pivots:
August 22, 2014 at 1:05 pm
Luis Cazares (8/22/2014)
If you want one column for each benefit, check the following articles about Cross Tabs and Pivots:
Yeah, this is right, my first answer was wrong because I was giving you a way to return all the benefits as a delimited list in single column, not as multiple columns. I misread/misinterpreted the question.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply