0
id Name
1  A
2  C
2  D
1  B

Output required through T-sql 

id Name
1  A,B
2  C,D
Marcin Nabiałek
  • 109,655
  • 42
  • 258
  • 291
Ankur Alankar Biswal
  • 1,184
  • 1
  • 12
  • 23

2 Answers2

0

Use this

;
WITH    SampleData
          AS ( SELECT   *
               FROM     ( VALUES ( 1, 'A'), ( 2, 'C'), ( 2, 'D'), ( 1, 'B'),
                        ( 1, 'C' ) ) T ( id, name )
             )
    SELECT  DISTINCT
            B.Id ,
            SUBSTRING(( SELECT  ',' + Name
                        FROM    SampleData AS A
                        WHERE   A.Id = B.ID
                      FOR
                        XML PATH('')
                      ), 2, 1000) AS NAME
    FROM    SampleData AS B

output result

enter image description here

Vasily
  • 5,707
  • 3
  • 19
  • 34
0

Here's another option:

SELECT   test.id,  test.Name+','+ Table1_1.Name AS Name
FROM         (SELECT     MIN(Name) AS Name, id
                       FROM          Table1
                       GROUP BY id) AS test RIGHT OUTER JOIN
                      Table1 AS Table1_1 ON test.id = Table1_1.id
                        where test.Name <>Table1_1.Name
Vin Vill
  • 24
  • 5
  • this will not work when id will be replicated more than two times – Vasily Mar 06 '15 at 18:03
  • Are you saying if there was another row of 1,A or something like 1,E? if my values look something like below, it would still show just the two distinct values: VALUES (1, 'A'), (2, 'C'), (2, 'D'), (1, 'B'), (1, 'A'), (1, 'A'), (2, 'C') – Vin Vill Mar 06 '15 at 19:14
  • 1
    try with VALUES (1, 'A'), (1, 'B'), (1, 'C'), (1, 'D') and you will see what I'm talking about – Vasily Mar 06 '15 at 19:32
  • ok, that makes sense. I was looking it as creating pairs of data (A,B), (C,D), (A,E) ,etc, not combining all Names into one row grouped by the ID. – Vin Vill Mar 06 '15 at 20:06
  • your solution works well for pairs, but "what if" also need to be taking into consideration – Vasily Mar 06 '15 at 20:40