字段1 字段2 字段3
10000 20080202 A
10000 20080202 E
10000 20080202 O
10000 20080303 B
10001 20080202 B
10001 20080303 A
10001 20080303 O 我想实现结果:
10000 20080202 A E O
10000 20080303 B
10001 20080202 B
10001 20080303 A O
10000 20080202 A
10000 20080202 E
10000 20080202 O
10000 20080303 B
10001 20080202 B
10001 20080303 A
10001 20080303 O 我想实现结果:
10000 20080202 A E O
10000 20080303 B
10001 20080202 B
10001 20080303 A O
begindeclare @t table
(
[c1] varchar(50),
[c2] varchar(50),
[c3] varchar(500)
)insert into @t([c1],[c2])
select distinct [字段1],[字段2]
from [表名称]declare @a varchar(50)
declare @b varchar(50)
declare @c varchar(500)declare cur_Item cursor for select [c1],[c2] from @t
open cur_Item
FETCH NEXT FROM cur_Item INTO @a, @b
while @@fetch_status = 0
BEGIN
set @c = ''
select @c=@c+isnull([字段3],'') from tb1 where [字段1]=@a and [字段2]=@b
update @t set [c3]=@c where [c1]=@a and [c2]=@b FETCH NEXT FROM cur_Item INTO @a, @b
END
CLOSE cur_Item
DEALLOCATE cur_Itemselect * from @tend
上面的办法有些笨,希望有好的方法贴出来学习一下。
insert #t
select 10000, 20080202, 'A' union
select 10000, 20080202, 'E' union
select 10000, 20080202, 'O' union
select 10000, 20080303, 'B' union
select 10001, 20080202, 'B' union
select 10001, 20080303, 'A' union
select 10001, 20080303, 'O'select * from #t--select a, b, sum(c)
--from #t
--group by a, b
SELECT *
FROM(
SELECT DISTINCT
a, b
FROM #t
)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT c FROM #t N
WHERE a = A.a and b = A.b
FOR XML AUTO
), '<N c="', ','), '"/>', ''), 1, 1, '')
)N--- 结果
a b values
----------- ----------- --------
10000 20080202 A,E,O
10000 20080303 B
10001 20080202 B
10001 20080303 A,O