none
plz solve the given scenario

    Question

  • input table:                                 output table :

     id        value                           id           value

    1           A                                1             A,C,S

    2           B                                2             B,P

    1           C                               3              M

    1          S

    3           M

    2           P

    Friday, April 20, 2012 5:45 AM

Answers

  • try this :

    DECLARE @Table TABLE(id INT,value VARCHAR(10))
    
     INSERT INTO @Table
     SELECT 1,'A'
     UNION
     SELECT 2,'B'
     UNION
     SELECT 1,'C'
     UNION
     SELECT 1,'S'
     UNION
     SELECT 3,'M'
     UNION
     SELECT 2,'P'
     
    
    
    select DISTINCT ID,(SELECT STUFF ( (SELECT ',' + [value]
    FROM @Table t1 WHERE t1.ID = t2.ID  
    FOR XML PATH('')), 1, 1, '')) AS [DIsplay Name]
    FROM @Table t2 


    Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Sagar129 Monday, April 23, 2012 6:39 AM
    Friday, April 20, 2012 1:37 PM

All replies

  • There is a couple of methods, here's a good link

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


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

    Friday, April 20, 2012 6:04 AM
  • Hi Godspink,

    The above query gives output like this     id    value

                                                                     1      a,b,c

                                                                     2      f,g,

                                                                     3      h, ,

    but I want output like     Id    value

                                           1      a,b,c

                                           2      f,g

                                           3      h

    can you please help me this...

                                        

    Friday, April 20, 2012 10:05 AM
  • try this :

    DECLARE @Table TABLE(id INT,value VARCHAR(10))
    
     INSERT INTO @Table
     SELECT 1,'A'
     UNION
     SELECT 2,'B'
     UNION
     SELECT 1,'C'
     UNION
     SELECT 1,'S'
     UNION
     SELECT 3,'M'
     UNION
     SELECT 2,'P'
     
    
    
    select DISTINCT ID,(SELECT STUFF ( (SELECT ',' + [value]
    FROM @Table t1 WHERE t1.ID = t2.ID  
    FOR XML PATH('')), 1, 1, '')) AS [DIsplay Name]
    FROM @Table t2 


    Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Sagar129 Monday, April 23, 2012 6:39 AM
    Friday, April 20, 2012 1:37 PM
  • DECLARE @Table TABLE(id INT,value VARCHAR(10))
    
     INSERT INTO @Table
     SELECT 1,'A'
     UNION
     SELECT 2,'B'
     UNION
     SELECT 1,'C'
     UNION
     SELECT 1,'S'
     UNION
     SELECT 3,'M'
     UNION
     SELECT 2,'P'
     
    
    
    select DISTINCT ID,(SELECT STUFF ( (SELECT ',' + [value]
    FROM @Table t5 WHERE t5.ID = t2.ID  
    FOR XML PATH('')), 1, 1, '')) AS [DIsplay Name]
    FROM @Table t2 
    Thursday, April 26, 2012 8:16 PM