22,209
社区成员
发帖
与我相关
我的任务
分享
create table #store
(MNO int
,MNAME varchar(100)
,CITY varchar(100)
)
create table #gds
(PNO varchar(50)
,PNAME varchar(100)
,CITY varchar(100)
,COLOR varchar(100)
)
create table #sale
(MNO varchar(50)
,PNO varchar(50)
,QTY int
,COST decimal(18,2)
,SALE decimal(18,2)
)
insert into #sale
select '1101', '0002', 3 ,3.9 ,5.7 union all
select '1101', '0020', 30, 300, 360 union all
select '1101', '0002', 3 ,3.9 ,5.7 union all
select '1102', '0020', 35, 385, 445 union all
select '1103', '0022', 33, 33 ,36.3 union all
select '1103', '0024', 25, 12.5, 15 union all
select '1104', '0023', 34, 3400, 4080
/***************************木有汇总****************************
select a.PNO,a.MNO,
(select cast(t.QTY as varchar)+',',cast(t.COST as varchar)+',',cast(t.SALE as varchar)+','
from #sale t
where t.MNO=a.MNO and t.PNO=a.PNO
for XML path(''))as 'Sumary'
into #X
from #sale a
*************************************************************/
/****************************汇总后的*****************************/
select a.PNO,a.MNO,
(select cast(sum(t.QTY) as varchar)+',',cast(sum(t.COST) as varchar)+',',cast(sum(t.SALE) as varchar)+','
from #sale t
where t.MNO=a.MNO and t.PNO=a.PNO
group by t.PNO,t.MNO
for XML path(''))as 'Sumary'
into #X
from #sale a
group by a.PNO,a.MNO
Declare @SqlStr nvarchar(max)
set @SqlStr= 'select x.PNO,g.PNAME,g.COLOR'
select @SqlStr+=',case MNO when '''+z.MNO+''' then x.Sumary else ''0,0,0'' end as '''+z.MNO+'(QTY,Cost,Sale)'' '
from (select distinct MNO from #sale )z
set @SqlStr +='from #x x left join #gds g on g.PNO=x.PNO order by x.PNO'
--select @SqlStr
exec(@SqlStr)
drop table #gds
drop table #sale
drop table #store
drop table #X