Transform column values into one row

Question

Let's assume I have the following table:

-- 1. Create table 'Store'
CREATE TABLE Store 
(
    Customer int,
    Product varchar(50)
);
    
-- 2. Add values to the table:
INSERT INTO Store
VALUES (1, 'Carrot'),
       (1, 'Milk'),
       (1, 'Bread'),
       (1, 'Eggs'),
       (2, 'Water'),
       (2, 'Juice'),
       (2, 'Wine'),
       (2, 'Coffee'),
       (3, 'Chicken'),
       (3, 'Tea');

I want to change the table in order to see all customer's products in one row like shown below:

Customer	Products
1	Carrot,Milk,Bread,Eggs
2	Water,Juice,Wine,Coffee
3	Chicken,Tea

Thanks in advance for your help!

@nbk I'm assuming the OP isn't stuck on SQL Server 2005... They added `STRING_AGG` to SQL Server 2017, which is now fairly established so I assume the OP can use it. — Dai, Sep 03 '22 at 13:59
@Dai all what you posted is there in the canonical answer, so this is a duplicate — nbk, Sep 03 '22 at 14:20

Dai · Accepted Answer · 2022-09-03T13:58:10.140

0

SELECT
    s.Customer,
    STRING_AGG( s.Product, ','  ) AS "Products"
FROM
    Store AS s
GROUP BY
    s.Customer
ORDER BY
    s.Customer;

Screenshot proof:

edited Sep 03 '22 at 13:58

answered Sep 03 '22 at 13:49

Dai

141,631
28
261
374

Transform column values into one row

1 Answers1