日期:2014-05-17 浏览次数:20606 次
create table tx
(id int,name varchar(10),orderid varchar(10))
insert into tx
select 1,'小红','S0001' union all
select 2,'小明','S0001' union all
select 3,'小蓝','S0002' union all
select 4,'小绿','S0003' union all
select 5,'小黄','S0003' union all
select 6,'小黑','S0001'
select row_number() over(order by getdate()) 'id',
a.orderid,
stuff((select ','+name from tx b
where b.orderid=a.orderid
for xml path('')),1,1,'') 'name'
from tx a
group by a.orderid
/*
id orderid name
-------------------- ---------- ------------------
1 S0001 小红,小明,小黑
2 S0002 小蓝
3 S0003 小绿,小黄
(3 row(s) affected)
*/
CREATE TABLE temp
(
id INT,
NAME NVARCHAR(10),
orderid VARCHAR(10)
)
INSERT temp
SELECT 1, N'小红', 'S0001' UNION ALL
SELECT 2, N'小明', 'S0001' UNION ALL
SELECT 3, N'小蓝', 'S0002' UNION ALL