我的问题是如何把一列的内容变成一行显示。例如
货物ID 供应商 采购数量
1 AAA 10
1 BBB 30
2 AAA 15
2 BBB 40
2 CCC 60
2 DDD 70
(其中AAA,BBB,是重要供应商,除了这两个供应商外就全部统计到其它供应商中)
我要显示的如何
货物ID 供应商AAA 供应商BBB 其它供应商 采购总数
1 10 30 0 40
2 15 40 130 185
(其中的其它供应商在这里包括了CCC和DDD)
我用的是ACCESS数据库。最好是能用宏实现。
在这里先谢谢大家。
10 个解决方案
#1
这个问题,我好像回答了一次了
#2
select 货物ID,
sum(iff(供应商='AAA',采购数量,0) as '供应商AAA',
sum(iff(供应商='BBB',采购数量,0) as '供应商BBB',
sum(iff((供应商 <>'AAA' and 供应商 <>'BBB'),采购数量,0) as '其他供应商',
sum(采购数量) as '采购总数'
from 表
group by 货物ID
sum(iff(供应商='AAA',采购数量,0) as '供应商AAA',
sum(iff(供应商='BBB',采购数量,0) as '供应商BBB',
sum(iff((供应商 <>'AAA' and 供应商 <>'BBB'),采购数量,0) as '其他供应商',
sum(采购数量) as '采购总数'
from 表
group by 货物ID
#3
上面那个写错了,这个才是对的:
select 货物ID,
sum(iif(供应商='AAA',采购数量,0)) as '供应商AAA',
sum(iif(供应商='BBB',采购数量,0)) as '供应商BBB',
sum(iif(供应商 <>'AAA' and 供应商 <>'BBB',采购数量,0)) as '其他供应商',
sum(采购数量) as '采购总数'
from t
group by 货物ID;
select 货物ID,
sum(iif(供应商='AAA',采购数量,0)) as '供应商AAA',
sum(iif(供应商='BBB',采购数量,0)) as '供应商BBB',
sum(iif(供应商 <>'AAA' and 供应商 <>'BBB',采购数量,0)) as '其他供应商',
sum(采购数量) as '采购总数'
from t
group by 货物ID;
#4
楼上的答案不错。学习一下。
#5
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+' from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id AAA BBB CCC DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70
*/
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+' from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id AAA BBB CCC DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70
*/
#6
我的方法支持供应商不定的情况
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+' from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70
*/
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+' from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70
*/
#7
上楼的很好...我测试了下..但没有合计总数
#8
再加总计就是了
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+',[采购总数] = sum(采购数量) from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD 采购总数
----------- ----------- ----------- ----------- ----------- -----------
1 10 30 0 0 40
2 15 40 60 70 185
*/
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+',[采购总数] = sum(采购数量) from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD 采购总数
----------- ----------- ----------- ----------- ----------- -----------
1 10 30 0 0 40
2 15 40 60 70 185
*/
#9
UP一下
#10
谢谢"一刀"及大家,这个问题是提了一次,但上次我测试是没有得到结果,现在是正确的。
#1
这个问题,我好像回答了一次了
#2
select 货物ID,
sum(iff(供应商='AAA',采购数量,0) as '供应商AAA',
sum(iff(供应商='BBB',采购数量,0) as '供应商BBB',
sum(iff((供应商 <>'AAA' and 供应商 <>'BBB'),采购数量,0) as '其他供应商',
sum(采购数量) as '采购总数'
from 表
group by 货物ID
sum(iff(供应商='AAA',采购数量,0) as '供应商AAA',
sum(iff(供应商='BBB',采购数量,0) as '供应商BBB',
sum(iff((供应商 <>'AAA' and 供应商 <>'BBB'),采购数量,0) as '其他供应商',
sum(采购数量) as '采购总数'
from 表
group by 货物ID
#3
上面那个写错了,这个才是对的:
select 货物ID,
sum(iif(供应商='AAA',采购数量,0)) as '供应商AAA',
sum(iif(供应商='BBB',采购数量,0)) as '供应商BBB',
sum(iif(供应商 <>'AAA' and 供应商 <>'BBB',采购数量,0)) as '其他供应商',
sum(采购数量) as '采购总数'
from t
group by 货物ID;
select 货物ID,
sum(iif(供应商='AAA',采购数量,0)) as '供应商AAA',
sum(iif(供应商='BBB',采购数量,0)) as '供应商BBB',
sum(iif(供应商 <>'AAA' and 供应商 <>'BBB',采购数量,0)) as '其他供应商',
sum(采购数量) as '采购总数'
from t
group by 货物ID;
#4
楼上的答案不错。学习一下。
#5
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+' from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id AAA BBB CCC DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70
*/
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+' from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id AAA BBB CCC DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70
*/
#6
我的方法支持供应商不定的情况
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+' from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70
*/
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+' from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD
----------- ----------- ----------- ----------- -----------
1 10 30 0 0
2 15 40 60 70
*/
#7
上楼的很好...我测试了下..但没有合计总数
#8
再加总计就是了
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+',[采购总数] = sum(采购数量) from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD 采购总数
----------- ----------- ----------- ----------- ----------- -----------
1 10 30 0 0 40
2 15 40 60 70 185
*/
--测试数据
create table scores(货物ID int,供应商 varchar(10),采购数量 int)
go
insert into scores
select 1,'AAA',10 union
select 1,'BBB',30 union
select 2,'AAA',15 union
select 2,'BBB',40 union
select 2,'CCC',60 union
select 2,'DDD',70
go
declare @s varchar(8000)
set @s=''
select @s=@s+',[供应商'+dt+']=sum(case 供应商 when '''+dt+''' then 采购数量 else 0 end)'
from(select distinct dt= 供应商 from scores)a
exec('select 货物id'+@s+',[采购总数] = sum(采购数量) from scores group by 货物id')
go
--删除测试
drop table scores
/*
货物id 供应商AAA 供应商BBB 供应商CCC 供应商DDD 采购总数
----------- ----------- ----------- ----------- ----------- -----------
1 10 30 0 0 40
2 15 40 60 70 185
*/
#9
UP一下
#10
谢谢"一刀"及大家,这个问题是提了一次,但上次我测试是没有得到结果,现在是正确的。