Create new table concatenating fields from many records

Question

I have a large table that hold all our customer purchase. Each record contains the purchased item, the date of purchase and a lot of other fields that are uselessly replicated for each purchase.

I wish to create a stored procedure to periodically export a new table with only one record per client with all the dates and the purchased items concatenated into one text field separated by a conventional character or crlf.

So now my table "Purchases" is something like this:

ID  |Name|PurDate |PurItem |etc
0001|Jack|20100101|Art. 115|.......
0002|Jack|20100105|Art. 230|.......
0003|Jack|20120408|Art. 098|.......
0004|John|20150808|Art. 021|.......
0005|John|20160203|Art. 432|.......

The new table should look like:

ID  |Name|Purchase|etc
0001|Jack|20100101 Art. 115;20100105 Art. 230;20120408 Art. 098|.......
0002|John|20150808 Art. 021;20160203 Art. 432|.......

Have you any suggestion?

Look at this question: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server — Alex Yu, Mar 17 '16 at 12:31
My suggestion: Don't do it. keeping multiple values in a single column is bad design. — Zohar Peled, Mar 17 '16 at 12:32

score 0 · Accepted Answer · answered Mar 17 '16 at 12:36

try this code and customize to your requirement,

    CREATE PROCEDURE UPDATERECORDS
BEGIN

-- Create temp table to get distinct names
CREATE TABLE #TEMP (NAME NVARCHAR(100))
-- insert names
INSERT INTO #TEMP
SELECT DISTINCT NAME FROM PURCHASES
-- insert to your table
INSERT INTO [YOUR NEW TABLE]
SELECT 
ROW_NUMBER()OVER (ORDER BY NAME) ID, 
NAME = 
STUFF((SELECT ', ' + NAME
FROM PURCHASE A
WHERE A.NAME = B.NAME
FOR XML PATH('')), 1, 2, ''),
PURCHASE = 
STUFF((SELECT ', ' + PURCHASE
FROM PURCHASE A
WHERE A.NAME = B.NAME
FOR XML PATH('')), 1, 2, ''), ETC.....
FROM #TEMP B

END

Create new table concatenating fields from many records

1 Answers1