SELECT B.HWZD_MC,B.HWZD_DW,C.CKZD_MC,A.CKKC_KCSL INTO #TEMP FROM CKKC A,HWZD B,CKZD Cdeclare @sql varchar(8000) set @sql = 'select HWZD_MC as 货物名称,HWZD_DW as 计量单位' select @sql = @sql + ',sum(case CKZD_MC when '''+kid+''' then CKKC_KCSL else 0 end) as '+CKZD_MC from (select distinct CKZD_MC from #temp) as a select @sql = @sql+' from #TEMP group by HWZD_MC,HWZD_DW' exec(@sql) drop table #temp
SELECT B.HWZD_MC,B.HWZD_DW,C.CKZD_MC,A.CKKC_KCSL INTO #TEMP FROM CKKC A,HWZD B,CKZD Cdeclare @sql varchar(8000) set @sql = 'select HWZD_MC as 货物名称,HWZD_DW as 计量单位' select @sql = @sql + ',sum(case CKZD_MC when '''+CKKC_KCSL+''' then CKKC_KCSL else 0 end) as '+CKZD_MC from (select distinct CKZD_MC from #temp) as a select @sql = @sql+' from #TEMP group by HWZD_MC,HWZD_DW' exec(@sql) drop table #temp
SORRY: 改正如下 create table HWZD(HWZD_BH VarChar(20),HWZD_MC VarChar(100),HWZD_DW Varchar(20)) INSERT INTO HWZD VALUES('001','货物1','吨') INSERT INTO HWZD VALUES('002','货物2','米') INSERT INTO HWZD VALUES('003','货物3','公斤') INSERT INTO HWZD VALUES('004','货物4','吨') INSERT INTO HWZD VALUES('005','货物5','米')create table CKZD(CKZD_BH ChaR(4),CKZD_MC varChar(100)) INSERT INTO CKZD VALUES('8200','仓库1') INSERT INTO CKZD VALUES('8201','仓库2') INSERT INTO CKZD VALUES('8202','仓库3') INSERT INTO CKZD VALUES('8203','仓库4')CREATE TABLE CKKC(CKKC_CKBH ChaR(4),CKKC_HWBH varChar(20),CKKC_KCSL Int) INSERT INTO CKKC VALUES('8201','001',1100) INSERT INTO CKKC VALUES('8201','002',100) INSERT INTO CKKC VALUES('8201','001',200) INSERT INTO CKKC VALUES('8201','003',400) INSERT INTO CKKC VALUES('8202','001',110) INSERT INTO CKKC VALUES('8203','001',300) INSERT INTO CKKC VALUES('8201','005',10)SELECT B.HWZD_MC,B.HWZD_DW,C.CKZD_MC,A.CKKC_KCSL INTO #TEMP1 FROM CKKC A,HWZD B,CKZD C WHERE A.CKKC_CKBH=C.CKZD_BH AND A.CKKC_HWBH =B.HWZD_BHSELECT * FROM #TEMP1 declare @sql varchar(8000) set @sql = 'select HWZD_MC as 货物名称,HWZD_DW as 计量单位' select @sql = @sql + ',sum(case CKZD_MC when '''+ CKZD_MC +''' then CKKC_KCSL else 0 end) as '+CKZD_MC from (select distinct CKZD_MC from temp) as a select @sql = @sql+' from #TEMP1 group by HWZD_MC,HWZD_DW ORDER BY HWZD_MC' exec(@sql) drop table #temp1
INTO #TEMP
FROM CKKC A,HWZD B,CKZD Cdeclare @sql varchar(8000)
set @sql = 'select HWZD_MC as 货物名称,HWZD_DW as 计量单位'
select @sql = @sql + ',sum(case CKZD_MC when '''+kid+''' then CKKC_KCSL else 0 end) as '+CKZD_MC
from (select distinct CKZD_MC from #temp) as a
select @sql = @sql+' from #TEMP group by HWZD_MC,HWZD_DW'
exec(@sql)
drop table #temp
INTO #TEMP
FROM CKKC A,HWZD B,CKZD Cdeclare @sql varchar(8000)
set @sql = 'select HWZD_MC as 货物名称,HWZD_DW as 计量单位'
select @sql = @sql + ',sum(case CKZD_MC when '''+CKKC_KCSL+''' then CKKC_KCSL else 0 end) as '+CKZD_MC
from (select distinct CKZD_MC from #temp) as a
select @sql = @sql+' from #TEMP group by HWZD_MC,HWZD_DW'
exec(@sql)
drop table #temp
改正如下
create table HWZD(HWZD_BH VarChar(20),HWZD_MC VarChar(100),HWZD_DW Varchar(20))
INSERT INTO HWZD VALUES('001','货物1','吨')
INSERT INTO HWZD VALUES('002','货物2','米')
INSERT INTO HWZD VALUES('003','货物3','公斤')
INSERT INTO HWZD VALUES('004','货物4','吨')
INSERT INTO HWZD VALUES('005','货物5','米')create table CKZD(CKZD_BH ChaR(4),CKZD_MC varChar(100))
INSERT INTO CKZD VALUES('8200','仓库1')
INSERT INTO CKZD VALUES('8201','仓库2')
INSERT INTO CKZD VALUES('8202','仓库3')
INSERT INTO CKZD VALUES('8203','仓库4')CREATE TABLE CKKC(CKKC_CKBH ChaR(4),CKKC_HWBH varChar(20),CKKC_KCSL Int)
INSERT INTO CKKC VALUES('8201','001',1100)
INSERT INTO CKKC VALUES('8201','002',100)
INSERT INTO CKKC VALUES('8201','001',200)
INSERT INTO CKKC VALUES('8201','003',400)
INSERT INTO CKKC VALUES('8202','001',110)
INSERT INTO CKKC VALUES('8203','001',300)
INSERT INTO CKKC VALUES('8201','005',10)SELECT B.HWZD_MC,B.HWZD_DW,C.CKZD_MC,A.CKKC_KCSL
INTO #TEMP1
FROM CKKC A,HWZD B,CKZD C
WHERE A.CKKC_CKBH=C.CKZD_BH AND A.CKKC_HWBH =B.HWZD_BHSELECT * FROM #TEMP1
declare @sql varchar(8000)
set @sql = 'select HWZD_MC as 货物名称,HWZD_DW as 计量单位'
select @sql = @sql + ',sum(case CKZD_MC when '''+ CKZD_MC +''' then CKKC_KCSL else 0 end) as '+CKZD_MC
from (select distinct CKZD_MC from temp) as a
select @sql = @sql+' from #TEMP1 group by HWZD_MC,HWZD_DW ORDER BY HWZD_MC'
exec(@sql)
drop table #temp1