用表1得表2
按orderID排序 找出所有的name 并统一放到同一列中。
注意：只要SQL语句，能实现吗？存储过程都不需要。只要语句。
求大手。
SQL
------解决方案--------------------
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