请问，关于sqlserver 开窗函数的用法
不太理解开窗函数的用法，求教。
如下例
id group name
1 A jack
2 A jason
3 A jon
4 B rose
5 B jerry
6 B tang
结果
1 A jack,jason,jon
2 B rose,jerry,tang
sqlserver2008 求实现方法
[解决办法]
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tb N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb
/*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
*/
--SQL2005中的方法2
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb
select distinct
t.id,
STUFF(tt.value_list.value('/r[1]','varchar(100)') , 1,1,'') as value
from tb t
cross apply
(
select value_list =
(
select ',' + tt.value
from tb tt
where tt.id = t.id
for xml path(''),root('r'),type
)
)tt
