Pivot help (Stuff, XML Path?)

1

I'm working on a query that will pull in results from one of the columns into the same cell. For example, I have the following criteria:
Table name:.
is Mydatabase

Database
Database326×456 5.3 KB

field names and data:
MemberName MemberColors MemberAmt
Dave Red 5,000
Dave Blue 5,000
Dave Green 5,000
Tom Blue 6,000
Judy Green 7,000
Judy Blue 7,000
Sam Yellow 3,000
Sam Red 3,000
Sam Blue 3,000

My current results repeat the member amounts. I just want the MemberColors all in one cell with a comma separater.
So my results would look like this:

MemberName MemberColors MemberAmt
Dave Red, Blue, Green 5,000
Tom Blue 6,000
Judy Green, Blue 7,000
Sam Yellow, Red, Blue 3,000

2

use string_agg()

3

hi

hope this helps

create data script

-- Create table
CREATE TABLE Members (
MemberName VARCHAR(50),
MemberColors VARCHAR(50),
MemberAmt INT
);

-- Insert data
INSERT INTO Members (MemberName, MemberColors, MemberAmt)
VALUES
('Dave', 'Red', 5000),
('Dave', 'Blue', 5000),
('Dave', 'Green', 5000),
('Tom', 'Blue', 6000),
('Judy', 'Green', 7000),
('Judy', 'Blue', 7000),
('Sam', 'Yellow', 3000),
('Sam', 'Red', 3000),
('Sam', 'Blue', 3000);

-- XML 

SELECT  
    MemberName,  
    STUFF((SELECT ', ' + MemberColors   
            FROM Members m2   
            WHERE m1.MemberName = m2.MemberName AND m1.MemberAmt = m2.MemberAmt  
            FOR XML PATH('')), 1, 2, '') AS MemberColors,  
    MemberAmt  
FROM  
    Members m1  
GROUP BY  
    MemberName, MemberAmt  
ORDER BY  
    MemberName;

-- String_Agg

SELECT  
    MemberName,  
    STRING_AGG(MemberColors, ', ') AS MemberColors,  
    MemberAmt  
FROM  
    Members 
GROUP BY  
    MemberName, MemberAmt  
ORDER BY  
    MemberName;

image
image412×344 5.66 KB

4

The String_Agg worked perfectly, thank you. I tried the XML but I'm not familiar enough with how to create the table for Members m2. I'll keep trying that method to see if I can figure it out but in the mean time the other option worked great.
Thank you!