Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
plz solve the given scenario

已答复 plz solve the given scenario

  • Friday, April 20, 2012 5:45 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Friday, April 20, 2012 6:04 AM
     
     
    Sign In to Vote
    0

    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 10:05 AM
     
     
    Sign In to Vote
    0

    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 1:37 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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
    •  
     
  • Thursday, April 26, 2012 8:16 PM
     
      Has Code
    Sign In to Vote
    0
     
    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