100分求一个交叉表查询在线等
表的格式如下
日期 客户 销售数量
20061001 家乐福 200
20061002 上好佳 300
20061003 赠品 400
20061103 家乐新店 500
20061105 北乐小店 600
20061106 家乐福 200
20061006 北乐小店 200
20061107 赠品 400
现在生成如下格式：
客户 200610 200611
家乐福 200 200
...........
请大家帮忙
搜索了结果了，可是一下不太明白用Case语句如何生成。。
请大伙给个结果，同时说明一下原理，谢谢
授人以鱼，不如授人以渔。。谢谢
[解决办法]
select 客户,
200610 = sum(case when substring(cast(日期 as varchar),1,6) = '200610 ' then 销售数量 else 0 end),
200611 = sum(case when substring(cast(日期 as varchar),1,6) = '200611 ' then 销售数量 else 0 end)
from T
group by 客户
[解决办法]
参考：
--生成测试示例数据
create table t(类型 varchar(4),名称 varchar(10))
insert into t select 'a ', '123 '
insert into t select 'a ', '444 '
insert into t select 'b ', '1we3 '
insert into t select 'b ', '4er '
insert into t select 'b ', 'dd '
insert into t select 'b ', 'ddvg '
go
--获得分组编号的SQL语句示例
select
a.*,
(select count(*) from t where 类型=a.类型 and 名称 <=a.名称) as Num
from
t a
go
--编号的SQL语句输出的结果
/*
类型 名称 Num
---- ---------- -----------
a 123 1
a 444 2
b 1we3 1
b 4er 2
b dd 3
b ddvg 4
*/
--创建动态SQL构建交叉表的存储过程
create procedure sp_test
as
begin
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',[名称 '+rtrim(num)+ ']=max(case num when '+rtrim(num)+ ' then 名称 end) '
from (select (select count(*) from t where 类型=a.类型 and 名称 <=a.名称) as Num from t a) b
group by num order by num
set @sql= 'select 类型 '+@sql+ ' from (select a.*,(select count(*) from t where 类型=a.类型 and 名称 <=a.名称) as Num from t a) b group by b.类型 '
exec(@sql)
end
go
--执行存储过程
exec sp_test
go
--查看存储过程执行结果
/*
类型 名称1 名称2 名称3 名称4
---- ---------- ---------- ---------- ----------
a 123 444 NULL NULL
b 1we3 4er dd ddvg
*/
--删除测试环境
drop procedure sp_test
drop table t
go
[解决办法]
--写多了
--生成测试示例数据
create table t(日期 datetime, 客户 varchar(100),销售数量 int)
insert into t select '20061001 ', '家乐福 ', 200
insert into t select '20061002 ', '上好佳 ', 300
insert into t select '20061003 ', '赠品 ', 400
insert into t select '20061101 ', '家乐新店 ', 500
insert into t select '20061102 ', '北乐小店 ', 600
insert into t select '20061103 ', '家乐福 ', 700
go
--创建动态SQL
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+ ',[ '+rtrim(num)+ ']=max(case num when '+rtrim(num)+ ' then 销售数量 end) '
from (select Left(convert(varchar,日期,112),6) as Num from t a) b
group by num order by num
set @sql= 'select 客户 '+@sql+ ' from (select a.*,Left(convert(varchar,日期,112),6) as Num from t a) b group by b.客户 '
exec(@sql)
go
--删除测试环境
drop table t
go
[解决办法]
--日期字段為字符串類型時
create table T(日期 varchar(10), 客户 varchar(10), 销售数量 int)
insert T select '20061001 ', '家乐福 ', 200
union all select '20061002 ', '上好佳 ', 300
union all select '20061003 ', '赠品 ', 400
union all select '20061103 ', '家乐新店 ', 500
union all select '20061105 ', '北乐小店 ', 600
union all select '20061106 ', '家乐福 ', 200
union all select '20061006 ', '北乐小店 ', 200
union all select '20061107 ', '赠品 ', 400
declare @sql varchar(8000)
set @sql= 'select 客户, '
select @sql=@sql+quotename(日期)+ '=sum(case when left(日期, 6)= '+quotename(日期, ' ' ' ')+ ' then 销售数量 else 0 end), '
from (
select 日期=left(日期, 6) from T group by left(日期, 6)
)tmp
select @sql=left(@sql, len(@sql)-1), @sql=@sql+ ' from T group by 客户 '
exec(@sql)
--result
客户 200610 200611
---------- ----------- -----------
北乐小店 200 600
家乐福 200 200
家乐新店 0 500
上好佳 300 0
赠品 400 400
