question

dkurth avatar image
dkurth asked

Combine multiple values into one list per record

I am trying to combine two rows into one and have tried to follow examples online with coalesce and stuff xml paths, but keep getting errors. In our db a person can have multiple races attached to their record. I would like each person to show up as one row with all of their races in a list. QUERY: SELECT DISTINCT vji.SWID, Case when np.IsHispanic = 1 then 'Hispanic' else 'Non-Hispanic' end as Ethnicity, rc.RaceDescription as Race FROM view_JuvenileInfo vji JOIN NaturalPerson np on vji.EntityID = np.EntityID JOIN Race r on r.EntityID = vji.EntityID JOIN RaceCode rc on r.RaceCd = rc.RaceCd EXAMPLE OUTPUT: SWID Ethnicity Race 61454521 Hispanic American Indian / Alaska Native 61454521 Hispanic White The Output I am trying to get would be one row like this where the multiple races are combined in one row: SWID Ethnicity Race 61454521 Hispanic American Indian / Alaska Native, White
query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
You are nearly there. Use your query to capture all the data, then use the stuff/xml over that: declare @view_JuvenileInfo table (EntityID int, SWID int); declare @NaturalPerson table (EntityID int, IsHispanic bit); declare @Race table (EntityID int, RaceCd int); declare @RaceCode table (RaceCd int, RaceDescription varchar(50)); insert into @view_JuvenileInfo select 1, 61454521; insert into @NaturalPerson select 1, 1; insert into @Race select 1,100; insert into @Race select 1,200; insert into @RaceCode select 100, 'American Indian / Alaska Native'; insert into @RaceCode select 200, 'White'; with MultipleRaces_cte as ( SELECT vji.SWID, Case when np.IsHispanic = 1 then 'Hispanic' else 'Non-Hispanic' end as Ethnicity, rc.RaceDescription as Race FROM @view_JuvenileInfo vji JOIN @NaturalPerson np on vji.EntityID = np.EntityID JOIN @Race r on r.EntityID = vji.EntityID JOIN @RaceCode rc on r.RaceCd = rc.RaceCd ) select distinct MultipleRaces_cte.SWID, MultipleRaces_cte.Ethnicity, stuff(( select ', ' + Race from MultipleRaces_cte for xml path('')) as Race ,1,2,'') from MultipleRaces_cte;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.