谁能帮我把ACCESS的交叉表语句转换为SQL的交叉表语句?(在线等待)

[w3k]

TRANSFORM Sum(FeePaid.FeePdQty) AS FeePdQtyOfSum
SELECT Class.CYear, FeePaid.CID, FeePaid.PID, Payer.PName, Fee.YID
FROM ((FeePaid INNER JOIN Class ON FeePaid.CID = Class.CID) INNER JOIN Fee ON FeePaid.PFeeID = Fee.FeeID) INNER JOIN Payer ON (FeePaid.PID = Payer.PID) AND (FeePaid.CID = Payer.CID)
GROUP BY Class.CYear, FeePaid.CID, FeePaid.PID, Payer.PName, Fee.YID
PIVOT Fee.FeeType;
=========================================================
能把上述ACCESS的交叉表语句转换为SQL2K的交叉表语句：）？ 谢谢了

[jwdream2008]

SELECT Class.CYear, FeePaid.CID, FeePaid.PID, Payer.PName, Fee.YID 
FROM FeePaid 
INNER JOIN Class ON FeePaid.CID = Class.CID 
INNER JOIN Fee ON FeePaid.PFeeID = Fee.FeeID
INNER JOIN Payer ON (FeePaid.PID = Payer.PID) AND (FeePaid.CID = Payer.CID) 
GROUP BY Class.CYear, FeePaid.CID, FeePaid.PID, Payer.PName, Fee.YID 

[jia_guijun]

SELECT b.Class.CYear, a.FeePaid.CID, a.FeePaid.PID, d.PName, c.YID
FROM FeePaid a INNER JOIN Class b ON a.CID = b.CID
	INNER JOIN Fee c ON a.PFeeID = c.FeeID
	INNER JOIN Payer d ON a.PID = d.PID AND a.CID = d.CID
GROUP BY a.CYear, b.CID, b.PID, d.PName, c.YID

这样？

[ws_hgo]

建议贴下
表结构
及其测试数据

[w3k]

还是不对啊，继续 吧

[jwdream2008]

[code=SQL]SELECT Class.CYear, FeePaid.CID, FeePaid.PID, Payer.PName, Fee.YID ,Sum(FeePaid.FeePdQty)
FROM FeePaid
INNER JOIN Class ON FeePaid.CID = Class.CID
INNER JOIN Fee ON FeePaid.PFeeID = Fee.FeeID
INNER JOIN Payer ON (FeePaid.PID = Payer.PID) AND (FeePaid.CID = Payer.CID)
GROUP BY Class.CYear, FeePaid.CID, FeePaid.PID, Payer.PName, Fee.YID code]

[w3k]

feepaid表字段 feepdqty cid pid pfeeid
class表字段 cyear cid
payer表字段 pname pid
fee表字段 yid feeid feetype

[ai_li7758521]

友情帮顶

[ws_hgo]

[Quote=引用 6 楼 w3k 的回复:]
feepaid表字段 feepdqty cid pid pfeeid
class表字段 cyear cid
payer表字段 pname pid
fee表字段 yid feeid feetype
[/Quote]
要什么效果

[w3k]

要把学生对应的feetype全部列出来成一个交叉表

类似：学生姓名 数学 语文 英语 编程

[jwdream2008]

http://blog.sina.com.cn/s/blog_4af11cfd010009zb.html

[w3k]

他们写的SQL语句都对了 就是FEETYPE都没全部列出来

[w3k]

要显示这样的效果：
CYear CID PID PName YID 数学 语文 英语 delphi VC
2006届 060101 0600001 李海 03年下 0 88 0 0 99

Fee表是动态存取年度学科表的

[jwdream2008]

--1、行互列
--> --> (Roy)生成測試數據
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go
--2005方法:

动态:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')

生成静态:

select 
    [Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 

生成格式:

/*
Student 数学          物理          英语          语文          总成绩
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
张三      87          90          82          78          337

(2 行受影响)
*/

go

[w3k]

他们上述的SQL语句只达到这样的效果

CYear CID PID PName YID
2006届 060101 0600001 李海 06年下

我所要的效果是这样

CYear CID PID PName YID 数学 语文 英语 delphi VC
2006届 060101 0600001 李海 06年下 0 88 0 0 99

[jwdream2008]

LZ：为了便于大家解决问题，你把表结构，以及一些简单的数据示例给出来，方便解决啊！

[w3k]

feepaid表字段：
feepdqty cid pid pfeeid
88 060101 06010101 06-01
77 060101 06010101 06-02
66 060101 06010101 06-03


class表字段

cyear cid
2006届 060101

payer表字段
pname pid
李海 06010101

fee表字段
yid feeid feetype
06年学分制 06-01 数学
06年学分制 06-02 英语
06年学分制 06-03 语文
……

=====================================
我所要的效果是这样

CYear CID PID PName YID 数学 语文 英语 delphi VC
2006届 060101 0600001 李海 06年学分制 0 88 0 0 99