34,838
社区成员




declare @sql varchar(8000)
set @sql = 'select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME '
select @sql = @sql + ' , sum(case rtrim(FSTOCKNAME) + '','' + rtrim(FSTOCKNUMBER) when ''' + newcol + ''' then FBUQTY else 0 end) [' + left( newcol , charindex(',' , newcol) -1) + ']'
from (select distinct rtrim(FSTOCKNAME) + ',' + rtrim(FSTOCKNUMBER) newcol from tb) as a
set @sql = @sql + ' , sum(FBUQTY) 合计 from tb group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME'
解析为
select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME ,
sum(case rtrim(FSTOCKNAME) + ',' + rtrim(FSTOCKNUMBER) when '北京,02' then FBUQTY else 0 end) [北京] ,
sum(case rtrim(FSTOCKNAME) + ',' + rtrim(FSTOCKNUMBER) when '天津,01' then FBUQTY else 0 end) [天津] ,
sum(FBUQTY) 合计
from tb
group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME
declare @sql varchar(8000)
set @sql = 'select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME '
select @sql = @sql + ' , sum(case FSTOCKNAME when ''' + FSTOCKNAME + ''' then FBUQTY else 0 end) [' + FSTOCKNAME + ']'
from (select distinct FSTOCKNAME from tb) as a
set @sql = @sql + ' , sum(FBUQTY) 合计 from tb group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME'
exec(@sql)
解析为
select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME ,
sum(case FSTOCKNAME when '北京' then FBUQTY else 0 end) [北京] ,
sum(case FSTOCKNAME when '天津' then FBUQTY else 0 end) [天津] ,
sum(FBUQTY) 合计
from tb
group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME
declare @sql varchar(8000)
set @sql = 'select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME '
select @sql = @sql + ' , sum(case FSTOCKNAME when ''' + FSTOCKNAME + ''' then FBUQTY else 0 end) [' + FSTOCKNAME + ']'
from (select distinct FSTOCKNAME from tb) as a
set @sql = @sql + ' , sum(FBUQTY) 合计 from tb group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME'
exec(@sql)
/*
FMATERIALNUMBER FMATERIALNAME FMATERIALMODEL FAUXPROPNUMBER FAUXPROPNAME FBUUNITNAME 北京 天津 合计
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------- ------------ ----------- ---------------------------------------- ---------------------------------------- ----------------------------------------
1001 产品1 A 001 红色 台 0 10 10
1001 产品1 A 002 兰色 台 0 20 20
1002 产品2 B 001 红色 台 20 10 30
1003 产品3 C 001 红色 台 0 30 30
1004 产品4 D 001 红色 台 40 0 40
*/
--搞定.
CREATE TABLE [dbo].[tb] (
[FMATERIALNUMBER] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FMATERIALNAME] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FMATERIALMODEL] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FAUXPROPNUMBER] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,
[FAUXPROPNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FBUUNITNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FBUQTY] [numeric](18, 0) NULL ,
[FSTOCKNUMBER] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FSTOCKNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1001','产品1','A','001','红色','台',10,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1001','产品1','A','002','兰色','台',20,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1002','产品2','B','001','红色','台',10,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1002','产品2','B','001','红色','台',20,'02','北京')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1003','产品3','C','001','红色','台',30,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1004','产品4','D','001','红色','台',40,'02','北京')
go
declare @sql varchar(8000)
set @sql = 'select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME '
select @sql = @sql + ' , sum(case rtrim(FSTOCKNAME) + '','' + rtrim(FSTOCKNUMBER) when ''' + newcol + ''' then FBUQTY else 0 end) [' + left( newcol , charindex(',' , newcol) -1) + ']'
from (select distinct rtrim(FSTOCKNAME) + ',' + rtrim(FSTOCKNUMBER) newcol from tb) as a
set @sql = @sql + ' , sum(FBUQTY) 合计 from tb group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME'
exec(@sql)
drop table tb
/*
FMATERIALNUMBER FMATERIALNAME FMATERIALMODEL FAUXPROPNUMBER FAUXPROPNAME FBUUNITNAME 北京 天津 合计
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------- ------------ ----------- ---------------------------------------- ---------------------------------------- ----------------------------------------
1001 产品1 A 001 红色 台 0 10 10
1001 产品1 A 002 兰色 台 0 20 20
1002 产品2 B 001 红色 台 20 10 30
1003 产品3 C 001 红色 台 0 30 30
1004 产品4 D 001 红色 台 40 0 40
*/
CREATE TABLE [dbo].[tb] (
[FMATERIALNUMBER] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FMATERIALNAME] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FMATERIALMODEL] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FAUXPROPNUMBER] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,
[FAUXPROPNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FBUUNITNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FBUQTY] [numeric](18, 0) NULL ,
[FSTOCKNUMBER] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FSTOCKNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1001','产品1','A','001','红色','台',10,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1001','产品1','A','002','兰色','台',20,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1002','产品2','B','001','红色','台',10,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1002','产品2','B','001','红色','台',20,'02','北京')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1003','产品3','C','001','红色','台',30,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1004','产品4','D','001','红色','台',40,'02','北京')
go
declare @sql varchar(8000)
set @sql = 'select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME '
select @sql = @sql + ' , sum(case FSTOCKNAME when ''' + FSTOCKNAME + ''' then FBUQTY else 0 end) [' + FSTOCKNAME + ']'
from (select distinct FSTOCKNAME from tb) as a order by FSTOCKNAME desc
set @sql = @sql + ' , sum(FBUQTY) 合计 from tb group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME'
exec(@sql)
drop table tb
/*\
FMATERIALNUMBER FMATERIALNAME FMATERIALMODEL FAUXPROPNUMBER FAUXPROPNAME FBUUNITNAME 天津 北京 合计
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------- ------------ ----------- ---------------------------------------- ---------------------------------------- ----------------------------------------
1001 产品1 A 001 红色 台 10 0 10
1001 产品1 A 002 兰色 台 20 0 20
1002 产品2 B 001 红色 台 10 20 30
1003 产品3 C 001 红色 台 30 0 30
1004 产品4 D 001 红色 台 0 40 40
*/
CREATE TABLE [dbo].[tb] (
[FMATERIALNUMBER] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FMATERIALNAME] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FMATERIALMODEL] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FAUXPROPNUMBER] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,
[FAUXPROPNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FBUUNITNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FBUQTY] [numeric](18, 0) NULL ,
[FSTOCKNUMBER] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FSTOCKNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1001','产品1','A','001','红色','台',10,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1001','产品1','A','002','兰色','台',20,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1002','产品2','B','001','红色','台',10,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1002','产品2','B','001','红色','台',20,'02','北京')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1003','产品3','C','001','红色','台',30,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1004','产品4','D','001','红色','台',40,'02','北京')
go
declare @sql varchar(8000)
set @sql = 'select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME '
select @sql = @sql + ' , sum(case FSTOCKNAME when ''' + FSTOCKNAME + ''' then FBUQTY else 0 end) [' + FSTOCKNAME + ']'
from (select distinct FSTOCKNAME from tb) as a
set @sql = @sql + ' , sum(FBUQTY) 合计 from tb group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME'
exec(@sql)
drop table tb
/*\
FMATERIALNUMBER FMATERIALNAME FMATERIALMODEL FAUXPROPNUMBER FAUXPROPNAME FBUUNITNAME 北京 天津 合计
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------- ------------ ----------- ---------------------------------------- ---------------------------------------- ----------------------------------------
1001 产品1 A 001 红色 台 0 10 10
1001 产品1 A 002 兰色 台 0 20 20
1002 产品2 B 001 红色 台 20 10 30
1003 产品3 C 001 红色 台 0 30 30
1004 产品4 D 001 红色 台 40 0 40
*/
--少了个合计.
CREATE TABLE [dbo].[tb] (
[FMATERIALNUMBER] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FMATERIALNAME] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FMATERIALMODEL] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[FAUXPROPNUMBER] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,
[FAUXPROPNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FBUUNITNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FBUQTY] [numeric](18, 0) NULL ,
[FSTOCKNUMBER] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[FSTOCKNAME] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1001','产品1','A','001','红色','台',10,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1001','产品1','A','002','兰色','台',20,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1002','产品2','B','001','红色','台',10,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1002','产品2','B','001','红色','台',20,'02','北京')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1003','产品3','C','001','红色','台',30,'01','天津')
insert into tb(FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FBUQTY,FSTOCKNUMBER,FSTOCKNAME)
values('1004','产品4','D','001','红色','台',40,'02','北京')
go
declare @sql varchar(8000)
set @sql = 'select FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FSTOCKNUMBER '
select @sql = @sql + ' , sum(case FSTOCKNAME when ''' + FSTOCKNAME + ''' then FBUQTY else 0 end) [' + FSTOCKNAME + ']'
from (select distinct FSTOCKNAME from tb) as a
set @sql = @sql + ' , sum(FBUQTY) 合计 from tb group by FMATERIALNUMBER,FMATERIALNAME,FMATERIALMODEL,FAUXPROPNUMBER,FAUXPROPNAME,FBUUNITNAME,FSTOCKNUMBER'
exec(@sql)
drop table tb
/*
FMATERIALNUMBER FMATERIALNAME FMATERIALMODEL FAUXPROPNUMBER FAUXPROPNAME FBUUNITNAME FSTOCKNUMBER 北京 天津 合计
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------- ------------ ----------- ------------ ---------------------------------------- ---------------------------------------- ----------------------------------------
1001 产品1 A 001 红色 台 01 0 10 10
1001 产品1 A 002 兰色 台 01 0 20 20
1002 产品2 B 001 红色 台 01 0 10 10
1002 产品2 B 001 红色 台 02 20 0 20
1003 产品3 C 001 红色 台 01 0 30 30
1004 产品4 D 001 红色 台 02 40 0 40
*/