asked
Comma Separated List in SQL
I’m trying to create a comma separated table by running the below query but this query has been running for 8 hours and I still don’t have any records in my table. SELECT DISTINCT connaics.* , itemlist INTO tbl7 FROM tbl2 connaics INNER JOIN ( SELECT customer , STUFF(( SELECT ',' + item FROM ( SELECT customer , item , ROW_NUMBER() OVER ( PARTITION BY customer, item ORDER BY customer ) AS rn FROM tbl2 ) firstRw WHERE rn = 1 AND firstRw.customer = tblitem.customer FOR XML PATH('') ), 1, 1, '') AS itemlist FROM tbl2 tblitem ) CommaList ON commalist.customer = connaics.customer My table has 4 million records of customers with a single item in each record. A customer can have multiple records with multiple different items and I want to list all the items that were purchased by that customer in another column. I do not want to list duplicate items. ![alt text][1] [1]: /storage/temp/2667-table.png Is there anything wrong with my code? Is there a better script that someone could share with me? Thanks for the help.
Code looks perfect to me, may be there need some performance improvement in it. Try some indexing. Let me know if it works for you. with CTE as ( select *, Row_Number() Over(partition by CustomerName,City,State,Item order by CustomerName,City,State,Item) RoWNum from OrigTable ), offsetrow as( select * from CTE where RoWNum =1 ) , offsetrow2 as (Select CustomerName, ----359 stuff(( Select ',' + Item from offsetrow p2 where p2.CustomerName = p1.CustomerName FOR XML PATH('') ),1,1,'') AS a from offsetrow p1 group by p1.CustomerName ) UPdate o set o.ItemKList = a.a from offsetrow2 a inner join OrigTable O On a.CustomerName = o.CustomerName
Hi, please let me know whether this code helps you out. SELECT t.custName ,t.City ,t.State ,T.Item ,STUFF((SELECT DISTINCT ', ' + CAST( Item AS VARCHAR(10)) [text()] FROM Cust WHERE custName = t.custName FOR XML PATH(''), TYPE) .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output FROM Cust t GROUP BY t.custName ,t.City ,t.State ,T.Item Regards, Mani G.S.,SELECT t.custName ,t.City ,t.State ,T.Item ,STUFF((SELECT DISTINCT ', ' + CAST( Item AS VARCHAR(10)) [text()] FROM Cust WHERE custName = t.custName FOR XML PATH(''), TYPE) .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output FROM Cust t GROUP BY t.custName ,t.City ,t.State ,T.Item,,Hi, Please let me know whether this one works for you. SELECT t.custName ,t.City ,t.State ,T.Item ,STUFF((SELECT DISTINCT ', ' + CAST( Item AS VARCHAR(10)) [text()] FROM Cust WHERE custName = t.custName FOR XML PATH(''), TYPE) .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output FROM Cust t GROUP BY t.custName ,t.City ,t.State ,T.Item I have taken OrigTable as Cust table. **Regards, Mani G.S.**
17 People are following this question.
