4

I have two linked tables in a SQL Server database, where one has information on individual plants, and the other contains owners (in separate rows) that are linked to that plant.

I want to "combine" those two through a join or something similar, but I want to do so in a way that condenses the list of owners into a single field, with the different owners separated by commas.

Here is code to create some test data:

CREATE TABLE Plant (
    PlantId   INTEGER       PRIMARY KEY,
    Capacity  DECIMAL(10,4) not null
);

CREATE TABLE PlantOwners (
    OwnerId   INTEGER       PRIMARY KEY,
    PlantFK   INTEGER       not null
    OwnerName VARCHAR(12)   not null
);

INSERT INTO Plant 
VALUES 
(1, 505.53),
(2, 251.4),
(3, 387.05),
(4, 179.65),
(5, 217.52)
;

INSERT INTO PlantOwners 
VALUES 
(1, 1, 'Apple'),
(2, 1, 'Microsoft'),
(3, 2, 'LG'),
(4, 2, 'CISCO'),
(5, 2, 'DELL'),
(6, 3, 'HP'),
(7, 3, 'Sony'),
(8, 3, 'Vornado'),
(9, 4, '3M'),
(10, 4, 'Microsoft'),
(11, 5, 'Apple')
;

And here is what the results would look like.

PlantId     Capacity     OwnerName
1           505.53       Apple, Microsoft
2           251.4        LG, CISCO, DELL
3           387.05       HP, Sony, Vornado
4           179.65       3M, Microsoft
5           217.52       Apple

I'm not that picky that it looks exactly like this, so if (for instance) it's easier to have the last item listed in each row of the Owners field have a comma after it, that's fine with me. So this would also be fine (or anything like this, really):

PlantId     Capacity     OwnerName
1           505.53       Apple, Microsoft,
2           251.4        LG, CISCO, DELL,
3           387.05       HP, Sony, Vornado,
4           179.65       3M, Microsoft,
5           217.52       Apple,
Emily Beth
  • 709
  • 1
  • 7
  • 23
  • Please refer to this https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv?rq=1 – Nagashree Hs Apr 11 '19 at 14:53
  • Excellent job posting the table and data!!! Unfortunately this has been asked and answered many many times. And worthy of +1 from me. ;) – Sean Lange Apr 11 '19 at 14:54
  • I'll just say that I searched by many different search terms, and hadn't found anything. Hard to find the other postings, since it's a weird thing to describe. I'm going through them now though. – Emily Beth Apr 11 '19 at 15:13
  • why dont you concatenate the column with comma after the povot? eg OwnerName+',' – JonWay Apr 11 '19 at 15:16
  • The linked similar questions all point to solutions that seem to have xml/escaping issues, or require SQL Server 2017 or later (which I don't have). I'll try to come up with and post a user-defined function, but my experience with SSMS/SQL user-defined functions is scant. So if anyone happens to have anything like that, that would be a huge help. – Emily Beth Apr 11 '19 at 15:51

0 Answers0