SQL多行合并为一行

--1
 SELECT [WORKFORM_ID], [CLYJ] = stuff 
  (   
    (SELECT ','+A .[CLYJ] FROM [tb_xa_ngboss_complainProcessInfo] A WHERE A.[WORKFORM_ID] =B. [WORKFORM_ID] FOR XML PATH('' )),1, 1,''  
  )  FROM [tb_xa_ngboss_complainProcessInfo] B
  GROUP BY [WORKFORM_ID]




if OBJECT_ID('[order]') is not null
    drop table [order]
go
create table [order](order_uid int,order_no nvarchar(20),car_no  nvarchar(20))  
go
if OBJECT_ID('CNTR') is not null
    drop table CNTR
go
create table CNTR(cntr_uid int,cntr_no nvarchar(20),order_uid int) 
go
insert into [order] values(1,'HKSZ0910230001 ','aaa')
insert into [order] values(2,'HKSZ0910230002 ','aaa')
insert into [order] values(3,'HKSZ0910230003 ','aaa')

insert into CNTR values(1,'CNTRNO1',1)
insert into CNTR values(2,'CNTRNO2',1)
insert into CNTR values(3,'CNTRNO1',2)
insert into CNTR values(4,'CNTRNO1',2)
insert into CNTR values(5,'CNTRNO2',3)

select order_no,cntr_no
from [order] o inner join CNTR R on O.order_uid=R.order_uid
--where o.order_no= '9999' --订单号
---sql2005才能这样写
Select order_no,
cntr_no=isnull(stuff((select ','+rtrim(cntr_no) from CNTR where [order].order_uid=CNTR.order_uid for XML path('')),1,1,''),'')
from [order]

/*
order_no             cntr_no
-------------------- --------------------
HKSZ0910230001       CNTRNO1
HKSZ0910230001       CNTRNO2
HKSZ0910230002       CNTRNO1
HKSZ0910230002       CNTRNO1
HKSZ0910230003       CNTRNO2

(5 行受影响)

order_no             cntr_no
-------------------- ------------------
HKSZ0910230001       CNTRNO1,CNTRNO2
HKSZ0910230002       CNTRNO1,CNTRNO1
HKSZ0910230003       CNTRNO2
*/
go