none
help me in solving this

    Question

  • i have two columns one column contains names and another customer nos, in name column names are repeated .

    my task is to put the single names i.e. unique names in another table names column and customer nos of dat names in a comma delimited form

    names customer no

    sumith 12456,987456,148852,148524

    rasul 456879,45645,415646

    i have sorted the names and compared it ,if i find names are equal i have inserted it in target table but i am unable to insert nos in comma delimited form.

    plz help me

    Tuesday, May 22, 2012 5:47 AM

Answers

  • Try

    select C.[Name], stuff((select ',' + convert(varchar(10), [Customer No]) from Customers C2 where C2.[Name] = C.[Name]
    
    order by [Customer No] FOR XML PATH('')),1,1,'') as [Customer Nos]
    
    from Customers C GROUP BY [Name]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by gurram999 Thursday, May 24, 2012 6:09 AM
    Thursday, May 24, 2012 4:20 AM
    Moderator

All replies

  • You can use For XML path to concatenate the rows into colums in your scenarios. Refer to the below link for details

    http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

    Tuesday, May 22, 2012 6:15 AM
  • begin
    declare @cus as table(cno int,cname varchar(20))
    insert into @cus(cno,cname)
    values(12456,'sumith')
    insert into @cus(cno,cname)
    values(987456,'sumith')
    insert into @cus(cno,cname)
    values(148852,'sumith')
    insert into @cus(cno,cname)
    values(148524,'sumith')
    insert into @cus(cno,cname)
    values(456879,'rasul')
    insert into @cus(cno,cname)
    values(45645,'rasul')
    insert into @cus(cno,cname)
    values(415646,'rasul')
    declare @names as table(cname varchar(20))
    insert into @names(cname)
    values('sumith')
    insert into @names(cname)
    values('rasul')
    select n.cname,
    stuff((select ',' + cast(cno as varchar(10))
    from @cus c where c.cname = n.cname order by cno for xml path('')),1,1,'') as 'cno'
    from @names n
    group by n.cname 
    end
    RESULTS
    --------
    cname	cno
    rasul	45645,415646,456879
    sumith	12456,148524,148852,987456

    Tuesday, May 22, 2012 10:37 AM
  • boss there are 8000+ rows can i keep on doing as u said
    Wednesday, May 23, 2012 12:54 PM
  • Try

    select C.[Name], stuff((select ',' + convert(varchar(10), [Customer No]) from Customers C2 where C2.[Name] = C.[Name]
    
    order by [Customer No] FOR XML PATH('')),1,1,'') as [Customer Nos]
    
    from Customers C GROUP BY [Name]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by gurram999 Thursday, May 24, 2012 6:09 AM
    Thursday, May 24, 2012 4:20 AM
    Moderator