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 SELECT 4, N'小绿', 'S0003' UNION ALL SELECT 5, N'小黄', 'S0003' UNION ALL SELECT 6, N'小黑', 'S0001'SELECT id = ROW_NUMBER() OVER(ORDER BY orderid), orderid, [name] = STUFF((SELECT ','+[name] FROM temp WHERE orderid=T.orderid ORDER BY ID FOR XML PATH('')),1,1,'') FROM temp T GROUP BY orderid
if object_id('tempdb..#a')>0 drop table #a go create table #a(id int not null,name varchar(20) null,orderid varchar(20) null) go insert #a select 1,'小红','S0001' union all select 1,'小明','S0001' union all select 1,'小蓝','S0002' union all select 1,'小绿','S0003' union all select 1,'小黄','S0003' union all select 1,'小黑','S0001' go select * from #a select * from ( select distinct orderid from #a ) a cross apply ( select stuff(replace(replace((select name from #a b where a.orderid=orderid for xml auto),'<b name="',','),'"/>',''),1,1,'') value ) b
果然高手, 我当时也问了一位大手,他也教了我一种实现的方法 if not object_id(N'Tempdb..#') is null drop table #table Go Create table #table([id] int,[name] nvarchar(1)) Insert #table select 1,N'a' union all select 1,N'b' union all select 2,N'c' union all select 2,N'd' union all select 2,N'e' Go Select OrderID,STUFF((SELECT ' '+Name FROM #table WHERE ID=a.ID FOR XML PATH('')) ,1,1,'') AS Name from #table AS a GROUP BY OrderID select * from #table同样可以实现。嘎嘎,,只是小弟不才,看不懂而已。非常感谢。。
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)
*/
(
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
SELECT 4, N'小绿', 'S0003' UNION ALL
SELECT 5, N'小黄', 'S0003' UNION ALL
SELECT 6, N'小黑', 'S0001'SELECT
id = ROW_NUMBER() OVER(ORDER BY orderid),
orderid,
[name] = STUFF((SELECT ','+[name] FROM temp WHERE orderid=T.orderid ORDER BY ID FOR XML PATH('')),1,1,'')
FROM temp T
GROUP BY orderid
drop table #a
go
create table #a(id int not null,name varchar(20) null,orderid varchar(20) null)
go
insert #a
select 1,'小红','S0001' union all
select 1,'小明','S0001' union all
select 1,'小蓝','S0002' union all
select 1,'小绿','S0003' union all
select 1,'小黄','S0003' union all
select 1,'小黑','S0001'
go
select * from #a
select * from
(
select distinct orderid from #a
) a
cross apply
(
select stuff(replace(replace((select name from #a b where a.orderid=orderid for xml auto),'<b name="',','),'"/>',''),1,1,'') value
) b
果然高手, 我当时也问了一位大手,他也教了我一种实现的方法 if not object_id(N'Tempdb..#') is null
drop table #table
Go
Create table #table([id] int,[name] nvarchar(1))
Insert #table
select 1,N'a' union all
select 1,N'b' union all
select 2,N'c' union all
select 2,N'd' union all
select 2,N'e'
Go
Select OrderID,STUFF((SELECT ' '+Name FROM #table WHERE ID=a.ID FOR XML PATH('')) ,1,1,'') AS Name
from #table AS a
GROUP BY OrderID
select * from #table同样可以实现。嘎嘎,,只是小弟不才,看不懂而已。非常感谢。。