Q:

Create new table concatenating fields from many records

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? 

A:

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

