x

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

Is there anything wrong with my code? Is there a better script that someone could share with me? Thanks for the help.

table.png (23.4 kB)
more ▼

asked Aug 03, 2015 at 02:45 AM in Default

avatar image

liton
910 35 41 47

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

 
more ▼

answered Aug 03, 2015 at 08:32 AM

avatar image

ammit.it2006
50 1 3 3

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 03, 2015 at 02:53 PM

avatar image

manigs
91 2 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1105
x304
x4

asked: Aug 03, 2015 at 02:45 AM

Seen: 80 times

Last Updated: Jan 09, 2016 at 07:36 AM

Copyright 2017 Redgate Software. Privacy Policy