x

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
more ▼

asked Aug 10 at 08:57 PM in Default

avatar image

dkurth
0

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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;

more ▼

answered Aug 11 at 07:45 AM

avatar image

Kev Riley ♦♦
66k 48 63 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x449

asked: Aug 10 at 08:57 PM

Seen: 16 times

Last Updated: Aug 11 at 07:45 AM

Copyright 2017 Redgate Software. Privacy Policy