Duplicates without using While or Cursor in T-SQL

Question

ID           Name
1            A
1            B
1            C
2            X
2            Y
3            P
3            Q
3            R

These are the columns in a table. I want to get output like

ID    Company
    1 A,B,C
    2 X, Y
    3 P,Q,R

Restriction is that I cannot use WHILE or CURSOR. Please write a query for the same.

Answer 1

This query should do it - uses FOR XML PATH which is new in SQL Server 2005 - hope you are on 2005 or higher, you didn't clearly specify.....

SELECT
    ID,
    STUFF(CAST((SELECT ','+Name FROM dbo.YourTable t2
                WHERE t2.ID = dbo.YourTable.ID
                FOR XML PATH(''), TYPE) AS VARCHAR(MAX)), 1, 1, '') AS 'Company'
FROM 
   dbo.YourTable
GROUP BY 
   ID

Answer 2

For 2005 version:

CREATE TABLE dbo.TEST([Type] INTEGER, [Name] NVARCHAR(100), [Qty] INTEGER)
GO
INSERT dbo.TEST VALUES(1, N'a', 5)
INSERT dbo.TEST VALUES(1, N'b', 6)
INSERT dbo.TEST VALUES(2, N'c', 44)
INSERT dbo.TEST VALUES(3, N'd', 1)
GO

select [Type],
 [Description] = replace((select [Name] + ':' + cast([Qty] as varchar) as 'data()'     
from TEST where [Type] = t.[Type] for xml path('')), ' ', ',')
from dbo.TEST t
group by [Type]

go

drop table dbo.TEST

Answer 3

Here's a solution using the CROSS APPLY method:

select id, sub.names
from (
    select distinct id from YourTable
) a
cross apply (
    select name + ', ' as [text()]
    from YourTable b
    where b.id = a.id
    for xml path('')
) sub(names)

Answer 4

You can group on the ID to get the unique values, then get the comma separated string for each using a for xml query:

select
  a.ID,
  substring((
    select ', ' + Name
    from Test1
    where Test1.ID = a.ID
    for xml path('')
  ), 3, 1000) as Company
from
  TheTable a
group by
  a.ID