CODEWITHSUNDEEP
sql-servert-sql
user7560542
user7560542

Reputation: 547

How would one concatenate one column from multiple rows and then insert that value into a single column of another row?

So let's say we have this table:

    CAR_PARTS
    ID   CAR_ID  PART_NAME       
    1    11      Steering Wheel  
    2    22      Steering Wheel 
    3    22      Headlights

And we also have this table:

    CARS
    ID   CAR_MODEL        PART_NAME_LIST
    11   Mustang          
    22   Camaro           
    33   F-150

How do add data to the PART_NAME_LIST column like this:

    CARS
    ID   CAR_MODEL        PART_NAME_LIST
    11   Mustang          Steering Wheel
    22   Camaro           Steering Wheel, Headlights
    33   F-150            (No parts were found)

Upvotes: 0

Views: 33

Answers (1)

Andrei Odegov
Andrei Odegov

Reputation: 3429

Ok. Try this for versions of SQL Server prior to SQL Server 2017:

declare @CAR_PARTS table(ID int, CAR_ID int, PART_NAME nvarchar(128));
insert @CAR_PARTS values
  (1, 11, 'Steering Wheel'),  
  (2, 22, 'Steering Wheel'), 
  (3, 22, 'Headlights');

declare @CARS table(
  ID int,
  CAR_MODEL nvarchar(30),
  PART_NAME_LIST nvarchar(max) null);

insert @CARS(ID, CAR_MODEL) values
  (11, 'Mustang'),
  (22, 'Camaro'),
  (33, 'F-150');

select
  c.ID,
  c.CAR_MODEL,
  stuff(cast((
    select
      ',' + cp.PART_NAME [text()]
    from @CAR_PARTS cp
    where cp.CAR_ID = c.ID
    for xml path('')
  )as nvarchar(max)), 1, 1, '') PART_NAME_LIST
from @CARS c;

In SQL Server 2017 it is possible to use the string_agg function:

select
  c.ID,
  c.CAR_MODEL,
  string_agg(cp.PART_NAME,',') PART_NAME_LIST
from @CARS c left join @CAR_PARTS cp
  on c.ID = cp.CAR_ID
group by c.ID, c.CAR_MODEL;

Check on this online here.

UPD: possible UPDATE statements were added to the demo.

Upvotes: 2

Related Questions