Sqlserver 多行合并为一行

多行合并为一行

表结构

子表结构如下：

父表结构如下：

由图可以看出子表和父表是通过父表ID进行关联的。

我们要实现的效果如下，查询父表数据的同时，增加一列来展示父表下子商品信息。

完整代码如下

select top {0} * from ( select  row_number() over(order by OP.ID desc) as rownumber,OP.ID,OP.procode,OP.proname,COUNT (1) OVER () AS totalcount,goodsqty=( SELECT CONVERT(NVARCHAR(100),o1.商品名)+','
FROM (SELECT DISTINCT pid,(goodsname+'['+CONVERT(varchar(100), goodsqty)+']') 商品名 FROM  OMS_ProductsDetail WITH (NOLOCK) WHERE pid=OP.ID)o1 WHERE o1.pid=OP.ID 
FOR xml Path('')),protype,proprice,starttime,isinvaild,
                                case isinvaild when 0 then '生效中' else '已失效' end as invailddesc,
                                invaildtime
from OMS_Products OP  where 1=1 {1} ) x where  rownumber>{2}
1
2
3
4
5
6

代码解析

第一步：把商品信息和库存组合到一起

goodsqty=( SELECT CONVERT(NVARCHAR(100),o1.商品名)+','
FROM (SELECT DISTINCT pid,(goodsname+'['+CONVERT(varchar(100), goodsqty)+']') 商品名 FROM  OMS_ProductsDetail WITH (NOLOCK) WHERE pid=OP.ID)o1 WHERE o1.pid=OP.ID 
FOR xml Path(''))
1
2
3

这里用到了两个知识点
1、WITH (NOLOCK)：其目的就是查询是不锁定表,从而达到提高查询速度的目的。
2、FOR xml Path(‘’)：把数据转换为xml。实现一行展示多条信息

第二步：实现分页

2012以下版本：
使用以下方式进行分页:

select top (@pagrrsize) * from 
(
  select row_number() over(order by Tablekey) as rownumber, * from tableName
) A
where rownumber > (@pagesize * (@pageindex-1))

1
2
3
4
5
6
7

2012以上版本：

select * from tableName order by Tablekey

offset (@pagesize(@pageindex-1))  

rows fetch next (@pagesize) 

rows only
1
2
3
4
5
6
7