品号 厂商 采购单号 未交数量 厂商未交汇总
031010300146 AAA POB047 500 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300146 AAA POB047 500 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300146 AAA POB048 600 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300146 BBB POB080 100 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300146 BBB POB080 250 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300147 BBB POB080 100 BBB(POB080-600)
031010300147 BBB POB080 500 BBB(POB080-600)
在数据库表wjmx中,现有字段品号,厂商,采购单号,未交数量,希望得到厂商未交汇总
规则:1.同一品号,如有多个厂商,之间用分号;隔开
2.同一品号,同一厂商,同一采购单号,汇总未交数量,不同采购单号之间用逗号,隔开
哪位知道,请解答下!
谢谢
031010300146 AAA POB047 500 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300146 AAA POB047 500 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300146 AAA POB048 600 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300146 BBB POB080 100 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300146 BBB POB080 250 AAA(POB047-1000,POB048-600);BBB(POB080-350)
031010300147 BBB POB080 100 BBB(POB080-600)
031010300147 BBB POB080 500 BBB(POB080-600)
在数据库表wjmx中,现有字段品号,厂商,采购单号,未交数量,希望得到厂商未交汇总
规则:1.同一品号,如有多个厂商,之间用分号;隔开
2.同一品号,同一厂商,同一采购单号,汇总未交数量,不同采购单号之间用逗号,隔开
哪位知道,请解答下!
谢谢
--测试数据
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.品号
参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
--测试数据
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 行受影响)*/
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',500select * 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 厂商未交汇总
) appdrop table #t