34,876
社区成员
发帖
与我相关
我的任务
分享两表关联汇总
如上图所示，两个表主料表和辅料表，得到一个对应产品的汇总表，主辅料的名称需要动态获取才行
主料表
ID 产品编号 物料名称 物料型号 数量
1 PN001 螺母 L01 50
2 PN001 螺母 L02 50
3 PN001 螺圈 100
4 PN002 螺母 L01 50
5 PN002 螺杆 50
6 PN003 螺母 L02 10
辅料表
ID 产品编号 辅料名称 辅料尺寸 数量
1 PN001 标签 7x5 100
2 PN001 纸箱 20x20x12 5
3 PN002 标签 7x5 50
4 PN004 皮筋 50
得到如下表格：
产品编号 所需主料 所需辅料
螺母 螺圈 螺杆 标签 纸箱 皮筋
PN001 100 100 100 5
PN002 50 50 50
PN003 10
PN004 50
...全文
请发表友善的回复…
发表回复
Dear SQL(燊) 2019-03-08
- 打赏
- 举报
if object_id('tempdb..#list') is not null drop table #list
;
with t1 as(
select ID=1,产品编号='PN001',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
select ID=2,产品编号='PN001',物料名称='螺母',物料型号='L02',数量=50 UNION ALL
select ID=3,产品编号='PN001',物料名称='螺圈',物料型号='',数量=100 UNION ALL
select ID=4,产品编号='PN002',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
select ID=5,产品编号='PN002',物料名称='螺杆',物料型号='',数量=50 UNION ALL
select ID=6,产品编号='PN003',物料名称='螺母',物料型号='L02',数量=10
),t2 as(
select ID=1,产品编号='PN001',辅料名称='标签',辅料尺寸='7x5' ,数量=100 union all
select ID=2,产品编号='PN001',辅料名称='纸箱',辅料尺寸='20x20x12' ,数量=5 union all
select ID=3,产品编号='PN002',辅料名称='标签',辅料尺寸='7x5' ,数量=50 union all
select ID=4,产品编号='PN004',辅料名称='皮筋',辅料尺寸='' ,数量=50
)
select 产品编号,物料名称,数量 into #list
from t1
union all
select 产品编号,物料名称=辅料名称,数量
from t2
declare @sql nvarchar(max),@colname nvarchar(4000)=''
;
with list as(
select distinct 物料名称
from #list
)
select @colname=@colname+',['+物料名称+']'
from list
set @sql='
select *
from #list lt pivot (sum(数量) for 物料名称 in('+RIGHT(@colname,len(@colname)-1)+')) pt
'
exec(@sql)
/*
产品编号 标签 螺杆 螺母 螺圈 皮筋 纸箱
----- ----------- ----------- ----------- ----------- ----------- -----------
PN001 100 NULL 100 100 NULL 5
PN002 50 50 50 NULL NULL NULL
PN003 NULL NULL 10 NULL NULL NULL
PN004 NULL NULL NULL NULL 50 NULL
(4 行受影响)
*/
snlixing 2019-03-08
- 打赏
- 举报
动态生成的怎么改呢
snlixing 2019-03-08
- 打赏
- 举报
谢谢大神，实际应用中[螺母],[螺圈],[螺杆],[标签],[纸箱 皮筋]都是动态的，不是直接写上的呢
Dear SQL(燊) 2019-03-08
- 打赏
- 举报
;
with t1 as(
select ID=1,产品编号='PN001',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
select ID=2,产品编号='PN001',物料名称='螺母',物料型号='L02',数量=50 UNION ALL
select ID=3,产品编号='PN001',物料名称='螺圈',物料型号='',数量=100 UNION ALL
select ID=4,产品编号='PN002',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
select ID=5,产品编号='PN002',物料名称='螺杆',物料型号='',数量=50 UNION ALL
select ID=6,产品编号='PN003',物料名称='螺母',物料型号='L02',数量=10
),t2 as(
select ID=1,产品编号='PN001',辅料名称='标签',辅料尺寸='7x5' ,数量=100 union all
select ID=2,产品编号='PN001',辅料名称='纸箱',辅料尺寸='20x20x12' ,数量=5 union all
select ID=3,产品编号='PN002',辅料名称='标签',辅料尺寸='7x5' ,数量=50 union all
select ID=4,产品编号='PN004',辅料名称='皮筋',辅料尺寸='' ,数量=50
),list as(
select 产品编号,物料名称,数量
from t1
union all
select 产品编号,物料名称=辅料名称,数量
from t2
)
select *
from list lt pivot (sum(数量) for 物料名称 in([螺母],[螺圈],[螺杆],[标签],[纸箱],[皮筋])) pt
/*
产品编号 螺母 螺圈 螺杆 标签 纸箱 皮筋
----- ----------- ----------- ----------- ----------- ----------- -----------
PN001 100 100 NULL 100 5 NULL
PN002 50 NULL 50 50 NULL NULL
PN003 10 NULL NULL NULL NULL NULL
PN004 NULL NULL NULL NULL NULL 50
(4 行受影响)
*/
Dear SQL(燊) 2019-03-08
- 打赏
- 举报
;
with t1 as(
select ID=1,产品编号='PN001',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
select ID=2,产品编号='PN001',物料名称='螺母',物料型号='L02',数量=50 UNION ALL
select ID=3,产品编号='PN001',物料名称='螺圈',物料型号='',数量=100 UNION ALL
select ID=4,产品编号='PN002',物料名称='螺母',物料型号='L01',数量=50 UNION ALL
select ID=5,产品编号='PN002',物料名称='螺杆',物料型号='',数量=50 UNION ALL
select ID=6,产品编号='PN003',物料名称='螺母',物料型号='L02',数量=10
),t2 as(
select ID=1,产品编号='PN001',辅料名称='标签',辅料尺寸='7x5' ,数量=100 union all
select ID=2,产品编号='PN001',辅料名称='纸箱',辅料尺寸='20x20x12' ,数量=5 union all
select ID=3,产品编号='PN002',辅料名称='标签',辅料尺寸='7x5' ,数量=50 union all
select ID=4,产品编号='PN004',辅料名称='皮筋',辅料尺寸='' ,数量=50
),list as(
select 产品编号,物料名称,数量
from t1
union all
select 产品编号,物料名称=辅料名称,数量
from t2
)
select *
from list lt pivot (sum(数量) for 物料名称 in([螺母],[螺圈],[螺杆],[标签],[纸箱 皮筋])) pt
/*
产品编号 螺母 螺圈 螺杆 标签 纸箱 皮筋
----- ----------- ----------- ----------- ----------- -----------
PN001 100 100 NULL 100 NULL
PN002 50 NULL 50 50 NULL
PN003 10 NULL NULL NULL NULL
PN004 NULL NULL NULL NULL NULL
(4 行受影响)
*/
snlixing 2019-03-08
- 打赏
- 举报
大神们，这个怎么写呢
