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

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 3

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 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

友情链接