SQL Server行转列

表的一列数据用“,”分割显示为行的一个单元格。

 

-- 订单表与订单明细表（一对多），明细表中GuestName字段的数据用","分割显示为一列。
declare @OrderNo nvarchar(128)
set @OrderNo = '14092400140'

select GuestName,MemberID from T_OrderDetail
where OrderNo = @OrderNo
select (select stuff((
select ' , '+GuestName From T_OrderDetail
where MemberID is not null and OrderNo = o.OrderNo
for xml path('')), 1, 1, '')) as CarryMember, --会员
(select stuff((
select ' , '+GuestName From T_OrderDetail
where MemberID is null and OrderNo = o.OrderNo
for xml path('')), 1, 1, '')) as CarryGuest --嘉宾
from T_order o
where o.OrderNo = @OrderNo