多行记要合并成1行-在线_Sql Server

多行记录合并成1行-----在线
我的SQL：
select OrderNumber,NodeID,'等待'as Action,DeptFlowTab.StatusName ,DeptFlowTab.ApproverName as Username from dbo.Stamp_Order as OrderTab
                    left join dbo.View_AdminApproveFlow as DeptFlowTab
                    on DeptFlowTab.NodeID=OrderTab.Status
                    where OrderTab.OrderNumber='{0}'  and DeptFlowTab.SegmentID={1} and DeptFlowTab.DeptID={2}

得出的结果是:

From0001,1000,等待,部门经理,张三
From0001,1000,等待,部门经理,李四
From0001,1000,等待,部门经理,王五

如果用函数或者其它方法得到：

From0001,1000,等待,部门经理,‘张三，李四，王五’
------解决思路----------------------


select OrderNumber,NodeID,'等待'as Action,DeptFlowTab.StatusName ,DeptFlowTab.ApproverName as Username into #temp from dbo.Stamp_Order as OrderTab
                    left join dbo.View_AdminApproveFlow as DeptFlowTab
                    on DeptFlowTab.NodeID=OrderTab.Status
                    where OrderTab.OrderNumber='{0}'  and DeptFlowTab.SegmentID={1} and DeptFlowTab.DeptID={2} 
select OrderNumber,NodeID,Action,StatusName, stuff((select ',' + [Username] from #temp t where OrderNumber=aa.OrderNumber and NodeID=aa.NodeID and StatusName=aa.StatusName for xml path('')) , 1 , 1 , '') from #temp as aa

------解决思路----------------------


with cte as 
(select OrderNumber,NodeID,'等待'as Action,DeptFlowTab.StatusName ,DeptFlowTab.ApproverName as Username from dbo.Stamp_Order as OrderTab
                    left join dbo.View_AdminApproveFlow as DeptFlowTab
                    on DeptFlowTab.NodeID=OrderTab.Status
                    where OrderTab.OrderNumber='{0}'  and DeptFlowTab.SegmentID={1} and DeptFlowTab.DeptID={2}
)
select stuff((select ','+Username from cte for xml path(''))),1,1,,'')