SQL之将多列合并一列

模板

select rent_id,
             devicesn = (stuff((select ',' + devicesn from  () AS a where a.rent_id =   
b.rent_id for xml path('')),1,1,''))
 from  () AS b group by  rent_id

实例

select rent_id,devicename = (stuff((select ',' + devicename from  (SELECT rd.rent_id, mc.name AS devicename,m.name AS devicemodel,ms.sn_number as devicesn 
FROM dbo.material_category mc INNER JOIN 
  dbo.material m ON m.category_id = mc.category_id
  INNER JOIN dbo.material_sn ms ON ms.material_id = m.material_id
  INNER JOIN dbo.rent_detail rd ON rd.sn_number = ms.sn_number) AS a where a.rent_id =   
b.rent_id for xml path('')),1,1,'')),
             devicemodel = (stuff((select ',' + devicemodel from  (SELECT rd.rent_id, mc.name AS devicename,m.name AS devicemodel,ms.sn_number as devicesn 
FROM dbo.material_category mc INNER JOIN 
  dbo.material m ON m.category_id = mc.category_id
  INNER JOIN dbo.material_sn ms ON ms.material_id = m.material_id
  INNER JOIN dbo.rent_detail rd ON rd.sn_number = ms.sn_number) AS a where a.rent_id =   
b.rent_id for xml path('')),1,1,'')),
             devicesn = (stuff((select ',' + devicesn from  (SELECT rd.rent_id, mc.name AS devicename,m.name AS devicemodel,ms.sn_number as devicesn 
FROM dbo.material_category mc INNER JOIN 
  dbo.material m ON m.category_id = mc.category_id
  INNER JOIN dbo.material_sn ms ON ms.material_id = m.material_id
  INNER JOIN dbo.rent_detail rd ON rd.sn_number = ms.sn_number) AS a where a.rent_id =   
b.rent_id for xml path('')),1,1,''))
 from  (SELECT rd.rent_id, mc.name AS devicename,m.name AS devicemodel,ms.sn_number as devicesn 
FROM dbo.material_category mc INNER JOIN 
  dbo.material m ON m.category_id = mc.category_id
  INNER JOIN dbo.material_sn ms ON ms.material_id = m.material_id
  INNER JOIN dbo.rent_detail rd ON rd.sn_number = ms.sn_number) AS b group by  rent_id

 

 

转载于:https://www.cnblogs.com/weixin18/p/9758937.html