SQL的行拆分、合并

行合并

要求是查询学生表，显示所有学生的爱好的结果集，代码如下

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (

SELECT sName,

(SELECT hobby+',' FROM student

  WHERE sName=A.sName

  FOR XML PATH('')) AS StuList

FROM student A

GROUP BY sName

) B

行拆分

use DB01;
-- 建表
create table dbo.tb_hobby(
id int null
,name varchar(10 null
,hobby varchar(100) null 
);
 
-- 插入数据
insert into dbo.tb_hobby values ('1001','朱梅拉','跑步,踢足球,打篮球');
insert into dbo.tb_hobby values ('1002','李格策','书法,跑步');
 
-- 查询
select * from dbo.tb_hobby; 
 
 
-- 拆分成行
select a.id
      ,a.name
   ,SUBSTRING(a.hobby,number,CHARINDEX(',',a.hobby+',',number)-number) as hobby
  from tb_hobby a,master..spt_values
 where number >= 1 and number < len(a.hobby)
   and type='p'
   and SUBSTRING(','+a.hobby,number,1)=',';

应用实例

--包装完成单
select 
	erp_cpgl_bz_main.djlb 单据类别,
	erp_cpgl_bz_main.djno 单据编号,
	erp_cpgl_bz_main.date1 制单日期,
	ERP_CPGL_BZ_detail.itemno 项目号,
	xsdd_dzit.MANo 型号,
	C.bjmc 隔热条类型,
	xsdd_dzit.SpLength 长度,
	ERP_CPGL_BZ_detail.weight 理论重量,
	ERP_CPGL_BZ_detail.dzc_weight 电子秤重量,
	ERP_CPGL_BZ_detail.sl 支数,
	ERP_CPGL_BZ_detail.zha_sl 扎数,
	ERP_CPGL_BZ_detail.bar_singlezL 米重,
	kingdee_sku_detail.kingdee_sku_name 成品sku,
	ERP_CPGL_BZ_detail.sku_end 成品sku,
	xsdd_dzit.xsdd_dzid 销售订单号,
	xsdd_dzit.Ex_ysdm 颜色代码,
	xsdd_dzit.Ex_ysmc 表面名称,
	xsdd_dzit.bzmc 包装名称,
	xsdd_dzit.Spbh 壁厚,
	xsdd_dzit.SingleWeight 支重,
	xsdd_dzit.SpMQName 材质名称,
	xsdd_dzit.IfComplete 是否完成,
	xsdd_dzit.rbmc 表面类别,
	xsdd_dzit.llzl 调整米重,
	xsdd_dzbh.custname 客户名称,
	xsdd_dzbh.bzmc1 包装备注,
	xsdd_dzbh.bz 订单备注
from
	ERP_CPGL_BZ_detail
left join erp_cpgl_bz_main on
	erp_cpgl_bz_main.djno = ERP_CPGL_BZ_detail.djno
left join xsdd_dzit on
	ERP_CPGL_BZ_detail.itemno = xsdd_dzit.ItemNo
LEFT JOIN xsdd_dzbh on
	xsdd_dzbh.xsdd_dzid = xsdd_dzit.xsdd_dzid
left join zd_madata_erp on
	xsdd_dzit.mano = zd_madata_erp.mano
left join (select sjmc,bjmc FROM ERP_ORDER_BASIC_ZHUHE_DETAIL where lv_type ='辅材') ERP_ORDER_BASIC_ZHUHE_DETAIL  on
	ERP_ORDER_BASIC_ZHUHE_DETAIL.sjmc	= xsdd_dzit.MANo
left JOIN (SELECT B.sjmc,LEFT(StuList,len(StuList)) as bjmc FROM (           ---stuff(StuList,1,1,'') 代替  len(StuList-1) 删除逗号
SELECT sjmc,
(SELECT CAST(bjmc as nvarchar) +' ,' FROM ERP_ORDER_BASIC_ZHUHE_DETAIL 
  WHERE sjmc=A.sjmc and lv_type ='辅材'
  FOR XML PATH('')) AS StuList
FROM ERP_ORDER_BASIC_ZHUHE_DETAIL A 
where lv_type ='辅材'
GROUP BY sjmc
) B) C
ON C.sjmc = xsdd_dzit.MANo
inner join  kingdee_sku_detail  on
kingdee_sku_detail.kingdee_sku_no = ERP_CPGL_BZ_detail.sku_end
where 
	erp_cpgl_bz_main.date1 >= '2021-06-01'
	AND erp_cpgl_bz_main.date1 < '2021-07-01'
--and left(ERP_CPGL_BZ_detail.sku_end,2) = '03'--穿条
and ( SUBSTRING(ERP_CPGL_BZ_detail.sku_end, 3 , 2) = '04' --木纹
	or SUBSTRING(ERP_CPGL_BZ_detail.sku_end, 3 , 2) = '18'--转印木纹（2D）
	or SUBSTRING(ERP_CPGL_BZ_detail.sku_end, 3 , 2) = '19')--抛花木纹（3D+2D）*/
order by erp_cpgl_bz_main.djno