***********做查询100天,遇到的最难问题**************

[einsteincao]

如何写出如下条件的Query

t_Customer:

id CustCode
1 John
2 Mike
3 Jully



t_Product:

id ProductCode
1 Bike
2 Car
3 Tractor




t_CustomerProductCombination:

CustomerCode ProductCode Tax
John Bike 2
John Car 500
Mike Tractor 149
Jully Bike 2
Jully Car 388
Jully Tractor 149


要查询出下面的结果集:

(No column name) John Mike Jully

Bike 2 2
Car 500 388
Tractor 149 149


形式有点像三维表...
实在不知道该如何去做

[tomhuang]

有点难度，先想想...

[zjcxc]

declare @s varchar(8000)
set @s=''
select @s=@s+',['+CustomerCode+']=sum(case CustomerCode when '''+CustomerCode+''' then Tax else 0 end)'
from t_CustomerProductCombination
group by CustomerCode
exec('select [No column name]=ProductCode'+@s+'
from t_CustomerProductCombination
group by ProductCode')

[realgz]

这么Easy，连排序规则都有了～～～～等几分钟，会有好多答案吧，我想。

[realgz]

楼上的，我讲这种话也抢我楼，严重不厚道。

[realgz]

楼上的估计少了排序，哈哈~~~~~~~

[einsteincao]

难道可以不用表变量?

[einsteincao]

搞不定啊邹建老大

Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a char data type as an argument.

[zjcxc]

--如果有按按各表id排序的要求

declare @s varchar(8000)
set @s=''
select @s=@s+',['+CustCode+']=sum(case a.CustomerCode when '''+CustCode+''' then a.Tax else 0 end)'
from t_Customer
order by id
exec('select [No column name]=a.ProductCode'+@s+'
from t_CustomerProductCombination a,t_Product b
where a.ProductCode=b.ProductCode
group by a.ProductCode,b.id
order by b.id')

[zjcxc]

--我以为你的 Tax 是数据型

--既然是char,则改sum为max


declare @s varchar(8000)
set @s=''
select @s=@s+',['+CustCode+']=max(case a.CustomerCode when '''+CustCode+''' then a.Tax else 0 end)'
from t_Customer
order by id
exec('select [No column name]=a.ProductCode'+@s+'
from t_CustomerProductCombination a,t_Product b
where a.ProductCode=b.ProductCode
group by a.ProductCode,b.id
order by b.id')