用SQL将多行字符串数据转换成一行数据(实例)

来源：互联网发布：淘宝全民疯抢设置入口编辑：程序博客网时间：2024/06/05 17:21
SELECT * FROM (SELECT * FROM (   SELECT o.OrderNo,          case o.IsCustomerUrgent when 0 then N'不加急' when 1 then N'加急' END as IsCustomerUrgent    FROM Orders o    WHERE OrderDate BETWEEN '2015-11-01 00:00:00' AND '2015-11-21 23:59:59' and OrderStatus ='90') t1    CROSS APPLY(      SELECT PName = (        SELECT PName,面料,米数,工艺 FROM(       SELECT s.OrderNo,          u.Fabric 面料 ,  u.MaterialUnit 米数,  Technology 工艺,   CONVERT(nvarchar(10),u.Quantity)+N'套'+u.ProductName PName   FROM Orders s, OrdersSuits u    WHERE u.OrderID = s.OrderID AND OrderDate BETWEEN '2015-11-01 00:00:00' AND '2015-11-21 23:59:59'    UNION ALL           SELECT s.OrderNo,          p.Fabric 面料,  p.MaterialUnit 米数,  Technology 工艺,  CONVERT(nvarchar(10),p.Quantity)+N'套'+p.ProductName PName    FROM Orders s, OrdersPants p    WHERE p.OrderID = s.OrderIDAND OrderDate BETWEEN '2015-11-01 00:00:00'AND '2015-11-21 23:59:59'   UNION ALL           SELECT s.OrderNo,          v.Fabric 面料,  v.MaterialUnit 米数,  Technology 工艺,CONVERT(nvarchar(10),v.Quantity)+N'套'+v.ProductName PName   FROM Orders s, OrdersVest v   WHERE v.OrderID = s.OrderIDAND OrderDate BETWEEN '2015-11-01 00:00:00'AND '2015-11-21 23:59:59'      UNION ALL           SELECT s.OrderNo,          c.Fabric 面料,  c.MaterialUnit 米数,  Technology 工艺,  CONVERT(nvarchar(10),c.Quantity)+N'套'+c.ProductName PName   FROM Orders s, OrdersCoats c   WHERE c.OrderID = s.OrderIDAND OrderDate BETWEEN '2015-11-01 00:00:00'AND '2015-11-21 23:59:59') t2  WHERE t2.OrderNo=t1.OrderNo FOR XML PATH(''), TYPE ).value('/', 'nvarchar(MAX)') )N)t3      WHERE t3.PName is not null and t3.PName <>N'' and t3.OrderNo is not null  and t3.OrderNo <>N'' order by t3.IsCustomerUrgent desc
0 0