100分求一个交叉表查询在线等

[jonescheng]

表的格式如下

日期 客户 销售数量
20061001 家乐福 200
20061002 上好佳 300
20061003 赠品 400
20061103 家乐新店 500
20061105 北乐小店 600
20061106 家乐福 200
20061006 北乐小店 200
20061107 赠品 400


现在生成如下格式：
客户 200610 200611
家乐福 200 200
...........


请大家帮忙
搜索了结果了，可是一下不太明白用Case语句如何生成。。

请大伙给个结果，同时说明一下原理，谢谢
授人以鱼，不如授人以渔。。谢谢

[lvcheng606717]

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 客户

[jonescheng]

日期是会不停增长的，
比如下面还有
200710
200712
这样的

[gc_ding]

参考：
--生成测试示例数据
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

[jonescheng]

也就是说：
200610 200611 200612 200701 200702
家乐福


日期是汇总前面的日期

[lvcheng606717]

行变列的方法很常用，原理就是将对应行的数据通过Case选到你要显示的列上。
这种方法一般都要通过group据何来实现。

Case语句这里是当作If... else... 来用的。

[jonescheng]

楼上的能说的详细点嘛，，
刚刚接触交叉表。。。
接合这个例子能说下吗？

[lvcheng606717]

时间是增长的话，需要动态构造此语句

declare @sql varchar(8000)

set @sql = 'select 客户'

select @sql = @sql + ',' +
substring(cast(日期 as varchar),1,6) +
'= sum(case when substring(cast(日期 as varchar),1,6) = '''+
substring(cast(日期 as varchar),1,6) +
''' then 销售数量 else 0 end),'
from T
group by substring(cast(日期 as varchar),1,6)

set @sql = @sql + 'from T group by 客户'

exec @sql

[jonescheng]

列 '日期' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

[jonescheng]

好象不能使用sub之类的字符串操作方法
使用后就出现上面的提示，
我删除掉这符串处理方法，就正常，可是出现很多重复列

[jonescheng]

谢谢旅程的耐心

[gc_ding]

--生成测试示例数据
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

--查看存储过程执行结果
/*
客户 销售数量1 销售数量2 销售数量3 销售数量4
---- ---------- ---------- ---------- ----------
a 123 444 NULL NULL
b 1we3 4er dd ddvg
*/

--删除测试环境
drop table t
go

--结果
/*
客户 200610 200611
----------------------------
北乐小店 NULL 600
家乐福 200 700
家乐新店 NULL 500
上好佳 300 NULL
赠品 400 NULL
*/

[gc_ding]

楼主把exec(@sql)改为print(@sql)就可以看到以下执行的语句了
select
客户,
[200610]=max(case num when 200610 then 销售数量 end),
[200611]=max(case num when 200611 then 销售数量 end)
from
(select a.*,Left(convert(varchar,日期,112),6) as Num from t a) b
group by b.客户

[jonescheng]

谢谢楼上的
去测试下，成功就回来结贴

[gc_ding]

--写多了
--生成测试示例数据
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

[marco08]

--日期字段為字符串類型時
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

[marco08]

--日期字段為日期類型時
create table T(日期 datetime, 客户 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 convert(char(6), 日期, 112)='+quotename(日期, '''')+' then 销售数量 else 0 end),'
from (
select 日期=convert(char(6), 日期, 112) from T group by convert(char(6), 日期, 112)
)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

[jonescheng]

不能对包含聚合或子查询的表达式执行聚合函数。

出现如下提示是什么原因？

[gc_ding]

楼主用我的不行么？

[gc_ding]

我测试了可以呀

[jonescheng]

谢谢楼上三位热心的朋友。。

成功了，，，
结贴，，谢谢大家
另还有一个问题，，，
在开贴