K3 根据零件代码查零件所用到的机床型号（多行数据合并成一行）

领导给了这样一张表，要求查这些零件在哪些机床上有用到。

1、先将代码取到，用EXCEL+记事本，略，做成这样先准备好

2、参考金蝶K3 Wise—BOM批量多级展开，将所有一级层级的机床代码连同下级物料都取出

----创建BOM父项表
create table HWbom
(
FId int identity(1,1),
FItemID int
)

----创建BOM子项表
create table HWbomchild
(
	FID int identity(1,1),
	FOrgID int,
	FParentID int,
	FLevel int,
	FSN nvarchar(200),
	FItemID int,
	FQty decimal(28,19),
	FBOMInterID int,
	FEntryID int
)

----将父项BOM对应的物料内码插入父项表（可带条件）
--t_ICItem——物料表
--t_item——基础资料主表
--icbom——BOM表
--ICBOMGROUP——BOM组别表
 
--物料表FErpClsID  物料属性（1-外购,2-自制,3-委外加工,5-虚拟件）
--基础资料主表	fdeleted   是否删除（0，未删除）  
--基础资料主表	FItemClassID	对应编码：
--1-客户
--2-部门
--3-职员
--4-商品
--5-仓位
--7-单位
--8-供应商
--基础资料主表	FDetail	是否明细（1，明细）
 
-- delete from HWbom                                                            
 
INSERT INTO HWbom ( FItemID ) SELECT
t.Fitemid 
FROM
	t_ICItem t --71615
	INNER JOIN t_item t5 ON t5.FItemID = t.Fitemid
	LEFT JOIN icbom t6 ON t6.fitemid= t.Fitemid
	LEFT JOIN ICBOMGROUP t7 ON t7.finterid= t6.fparentid 
WHERE
	t.FErpClsID IN ( 1, 2, 3, 5 ) --2代表自制件，3代表委外件，5代表虚拟件
	
	AND t5.FItemClassID= 4 
	AND t5.FDeleted= 0 
	AND t5.FDetail= 1 --类型是4（商品），未删除，明细是1 
	--and t5.fnumber in('物料编码1','物料编码2')	--*****可根据需要限定BOM范围
	and t5.fnumber like '1.%'----这次取整机的
ORDER BY
	t.fnumber



----根据父项表数据，将数据插入子项表，为后续卷算做准备
	INSERT INTO HWbomchild ( FOrgID, FParentID, FSN, FItemID, FQty, FBOMInterID, FEntryID, FLevel ) SELECT
	FId,- 1 AS FParentID,
	'001',
	u1.FItemID,
	1 AS Fqty,
	t1.FInterID AS FBOMInterID,- 1 AS FEntryID,
	0 
FROM
	HWbom u1
	LEFT JOIN ICBOM t1 ON u1.FItemID= t1.FItemID 
	AND t1.FUseStatus= 1072 


----BOM卷算
DECLARE
	@LEVEL INT 
	SET @LEVEL = 1
 
WHILE
		EXISTS ( 
            SELECT 1 FROM HWbomchild 
                WHERE FLevel = @LEVEL - 1 
                        AND FItemID 
                            IN ( SELECT Fitemid FROM icbom WHERE FUseStatus = 1072 ) 
                ) 
		AND @LEVEL < 20 
 
BEGIN
			INSERT INTO HWbomchild ( FOrgID, FParentID, FSN, FItemID, FQty, FBOMInterID, FEntryID, FLevel ) 
        SELECT
			u1.FOrgID,
			u1.FID,
			u1.FSN+ '.' + RIGHT ( '000' + CONVERT ( nvarchar ( 50 ), t2.Fentryid ), 3 ),
			t2.Fitemid,
			u1.FQty* ( t2.FQty/ t1.FQty ) / ( 1-t2.FScrap/ 100 ),
			t2.FInterID,
			t2.FEntryID,@LEVEL 
		FROM
			HWbomchild u1
			INNER JOIN icbom t1 ON u1.FItemID= t1.FItemID
			INNER JOIN ICBOMChild t2 ON t2.FInterID= t1.FInterID 
		WHERE
			u1.FLevel=@LEVEL - 1 
			AND t1.FUseStatus= 1072 
			
SET @LEVEL =@LEVEL + 1 
 
END 


----最终BOM展开数据检索
SELECT
	t2.FModel 专机号,
	t4.FBOMNumber BOM编号,
	t2.FNumber 产品代码,
	t2.FName 产品名称,
	t1.FSN 层级和序号,
	t3.FNumber 材料代码,
	t3.FName 材料名称,
	t3.FModel 材料规格,
	t1.FQty 产品用量,
	yy.fname 材料属性,
	t5.FQty 单位用量,
	t5.FScrap 损耗率,
	t6.FName AS 是否跳层,
	t5.FNote 备注,
	t5.FPositionNo 位置号 
	into ##temp0901bom -----建立一张临时表
		FROM
			HWbom u1
			INNER JOIN HWbomchild t1 ON u1.FId= t1.FOrgID
			INNER JOIN t_icitem t2 ON t2.FItemID= u1.FItemID
			INNER JOIN t_ICItem t3 ON t3.FItemID= t1.FItemID
			LEFT JOIN ICBOM t4 ON t4.FInterID= t1.FBOMInterID
			LEFT JOIN ICBOMChild t5 ON t5.FInterID= t1.FBOMInterID 
			AND t5.FEntryID= t1.FEntryID
			LEFT JOIN t_SubMessage t6 ON t6.FInterID= t4.FBOMSkip
			INNER JOIN t_SubMessage yy ON yy.FInterID= t3.FErpClsID
where  T4.FUseStatus = 1072 
and t4.FBomType <> 3 --FBomType；0-普通,1-配置类,2-生产规划类,3-客户BOM,4-特征类
-- and t4.fitemid=t2.fitemid
-- and t4.FBOMNumber = 'BOM019979'
        
order by u1.FId,t1.FSN





----TRUNCATE TABLE HWbom --清空附表数据
----TRUNCATE TABLE HWbomchild --清空子表数据

----DROP TABLE HWbom --删除父表
----DROP TABLE HWbomchild --删除子表

查询结果如下

3、结合第1步的代码，建一个临时表，目的是查询出零件用在哪些机床上。

CREATE TABLE ##TMP (
FNUMBER VARCHAR(20),
FNAME VARCHAR(1000)
);
INSERT INTO ##TMP ( FNUMBER,FNAME )
select 材料代码,产品名称 from  ##temp0901bom
where 材料代码 in ('3.01.002.00010','3.01.002.00015','3.01.002.00040',
......)

4、将机床型号列转行
可参考sqlserver函数多行数据合并成一行

SELECT
    fnumber,
    fname= (
               STUFF(
                    (SELECT ',' + fname
                     FROM ##tmp
                     WHERE fnumber= A.fnumber
                     FOR xml path('')
                    ),1,1,''
                    )
                 )
FROM ##tmp A
GROUP by fnumber;

5、后发现数据，有些为空的，查询一下了，有些物料并不放在BOM表中，是直接出库掉的，所以给出库的相关物料也进行归集

select aaa.物料代码 fnumber,isnull(aaa.jcmc,aaa.cbdx)  xm
into ##tmp_ck
from  (
Select v1.FBillNo 单据号,t4.fname 领用部门,t5.fname 用途类别,V1.FUse 领料用途,
convert(char(20),v1.FDate,111) 日期, 
   
t1.fnumber 物料代码,t1.fname 物料名称,t1.FModel 规格,
case 
when v1.FTranType=1 then '外购入库'
when v1.FTranType=2 then '产品入库'
when v1.FTranType=10 then '其他入库'
when v1.FTranType=21 then '销售出库'
when v1.FTranType=24 then '生产领料'
when v1.FTranType=29 then '其他入库'
end 
类别,t2.FName 出库仓库,
u1.FQty 数量,u1.FAmount 金额, e2.FName 领用人,e1.FName 制单人,
a.FNumber fnumber1,a.FName  cbdx
,tt2.fname jcmc,tt2.fnumber fnumber2

from ICStockBill v1 
inner  JOIN ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID  
inner join t_ICItem t1 on u1.FItemID=t1.FItemID
left join t_item a on u1.FCostOBJID=a.FItemID and a.FItemClassID=2001
 left  join t_Stock t2 on u1.FSCStockID=t2.FItemID  and t2.FItemID in (26598,66585)
 left join t_User e1 on v1.FBillerID=e1.FUserID
 left join t_emp e2 on v1.FSManagerID=e2.FItemID
 left join t_Department t4 on v1.FDeptID=t4.FItemID
 left join t_BOS257800004 t5 on v1.FHeadSelfB0435=t5.FID

left join (select 
t4.FNumber  fnumber ,t4.FName fname,t4.FModel fmodel from  
 SEOrderEntry t2  
inner join t_ICItem t4 on t2.FItemID=t4.FItemID 
where t4.FModel<>'') TT2
on V1.FUse=TT2.fmodel
 where 1=1 AND ( (v1.FStatus=1 AND  v1.FCancellation = 0))
--and( v1.FDate>='2020-01-01' and  v1.FDate<='2022-12-31')
and v1.FTranType in (21,24,29) 
and v1.FTranType=24 and (t1.FNumber in ('3.01.002.00010',
'3.01.002.00015',
'3.01.002.00040',
'3.01.002.00045',
......)
 )
)AAA

6、也按列值聚合成行值的方法

7、最后将这两块数据复制到EXCEL表中，并做引用