34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#wjmx') is null
drop table #wjmx
Go
Create table #wjmx([品号] nvarchar(32),[厂商] nvarchar(23),[采购单号] nvarchar(26),[未交数量] int,[厂商未交汇总] nvarchar(63))
Insert #wjmx
select N'031010300146',N'AAA',N'POB047',500,N'AAA(POB047-1000,POB048-600);BBB(POB080-350)' union all
select N'031010300146',N'AAA',N'POB047',500,N'AAA(POB047-1000,POB048-600);BBB(POB080-350)' union all
select N'031010300146',N'AAA',N'POB048',600,N'AAA(POB047-1000,POB048-600);BBB(POB080-350)' union all
select N'031010300146',N'BBB',N'POB080',100,N'AAA(POB047-1000,POB048-600);BBB(POB080-350)' union all
select N'031010300146',N'BBB',N'POB080',250,N'AAA(POB047-1000,POB048-600);BBB(POB080-350)' union all
select N'031010300147',N'BBB',N'POB080',100,N'BBB(POB080-600)' union all
select N'031010300147',N'BBB',N'POB080',500,N'BBB(POB080-600)'
Go
--测试数据结束
SELECT 品号 ,
STUFF(( SELECT DISTINCT ';' + #wjmx.厂商
FROM #wjmx
WHERE 品号 = a.品号
FOR
XML PATH('')
), 1, 1, '') AS 厂商
FROM #wjmx a
GROUP BY a.品号
create table #t (id int identity,品号 varchar(20),厂商 varchar(10),采购单号 varchar(20),未交数量 int)
insert into #t(品号,厂商,采购单号,未交数量)
select '031010300146','AAA','POB047',500
union all
select '031010300146','AAA','POB047',500
union all
select '031010300146','AAA','POB048',600
union all
select '031010300146','BBB','POB080',100
union all
select '031010300146','BBB','POB080',250
union all
select '031010300147','BBB','POB080',100
union all
select '031010300147','BBB','POB080',500
select * from #t a
cross apply(
select stuff((
select ';'+厂商+'('+stuff((select ','+采购单号+'-'+convert(varchar(max),sum(未交数量)) from #t where 品号=a.品号 and 厂商=b.厂商 group by 采购单号 for xml path('')),1,1,'')+')'
from #t b
where 品号=a.品号
group by 厂商
for xml path('')
),1,1,'') as 厂商未交汇总
) app
drop table #t
--测试数据
if not object_id(N'Tempdb..#wjmx') is null
drop table #wjmx
Go
Create table #wjmx([品号] nvarchar(32),[厂商] nvarchar(23),[采购单号] nvarchar(26),[未交数量] int)
Insert #wjmx
select N'031010300146',N'AAA',N'POB047',500 union all
select N'031010300146',N'AAA',N'POB047',500 union all
select N'031010300146',N'AAA',N'POB048',600 union all
select N'031010300146',N'BBB',N'POB080',100 union all
select N'031010300146',N'BBB',N'POB080',250 union all
select N'031010300147',N'BBB',N'POB080',100union all
select N'031010300147',N'BBB',N'POB080',500
Go
select * from #wjmx a join (
select [品号], [厂商未交汇总] = STUFF((select distinct ';' + [厂商] + '( ' +
STUFF((select distinct '-'+[采购单号]+'-'+ rtrim(sum([未交数量])) from #wjmx
where [品号] = a.[品号] and [厂商] = a.[厂商] group by [品号] ,[厂商],[采购单号] for XML path('')),1,1,'')
+ ')' from #wjmx a where [品号] = t.[品号] for xml path('') ),1,1,'' )
from #wjmx t
group by [品号] ) b on a.品号 = b.品号
/*
(7 行受影响)
品号 厂商 采购单号 未交数量 品号 厂商未交汇总
-------------------------------- ----------------------- -------------------------- ----------- -------------------------------- -----------------------------------------------
031010300146 AAA POB047 500 031010300146 AAA( POB047-1000-POB048-600);BBB( POB080-350)
031010300146 AAA POB047 500 031010300146 AAA( POB047-1000-POB048-600);BBB( POB080-350)
031010300146 AAA POB048 600 031010300146 AAA( POB047-1000-POB048-600);BBB( POB080-350)
031010300146 BBB POB080 100 031010300146 AAA( POB047-1000-POB048-600);BBB( POB080-350)
031010300146 BBB POB080 250 031010300146 AAA( POB047-1000-POB048-600);BBB( POB080-350)
031010300147 BBB POB080 100 031010300147 BBB( POB080-600)
031010300147 BBB POB080 500 031010300147 BBB( POB080-600)
(7 行受影响)
*/