group by 合并字符串

1.mysql

1. 
   
2. insert  into `jxc_orderdetail`(`detail_id`,`order_no`,`pro_no`,`pro_name`,`pro_number`,`pro_price`,`pay_price`) values (139704,'H140523000001','130001','美国莎娜琳',2,'78.00','1000.00'),(139705,'H140524000001','49101815101237','移花宫速效细肤祛痘乳100ML',1,'133.00','1000.00'),(139706,'H140524000001','ECS000160','移花宫速效控油嫩肤水12ML',1,'39.00','1000.00');  

SELECT order_no,GROUP_CONCAT(pro_name,'*',pro_number) FROM jxc_orderdetail GROUP BY order_no

2.ms sql server 2008,2012后使用concat函数即可

(1)

SELECT oi.*,  stuff((select ','+convert(varchar(25), GuestName) FROM guest g WHERE g.ordersn = oi.OrderSN AND oi.ItemId = g.ItemId FOR XML PATH('')), 1, 1, '') as CustomerNameFROM orderitem oi

以上涉及的两个表是OrderItem和Guest,以属性OrderSN和ItemId连接，查询结果格式为：

用户0,用户1,用户2

(2)

select
  BeginCity,EndCity,FanDian,
     [CangWei]=stuff((select '/'+[CangWei] from test1 t  
     where t.BeginCity=Test1.BeginCity  and t.EndCity=Test1.EndCity  and t.FanDian=Test1.FanDian   
     for xml path('')), 1, 1, '') 
from  
Test1 
group by 
BeginCity,EndCity,FanDian