有表A(CaseID1,CaseID2,CaseID3,CaseID4...)记录为:
A、B、C、D...
表B(MakeingID,GX,CaseID,Price)记录为:
M001,10,A,1.2
M002,20,B,1.3
M003,30,C,1.4
如何查询为:
MakeingID,GX, A , B ,C , D
M001, 10, 1.2
M002, 20, Null 1.3
M003, 30, Null Null 1.4请高手帮写出,即时给分,在线等,急!!!
A、B、C、D...
表B(MakeingID,GX,CaseID,Price)记录为:
M001,10,A,1.2
M002,20,B,1.3
M003,30,C,1.4
如何查询为:
MakeingID,GX, A , B ,C , D
M001, 10, 1.2
M002, 20, Null 1.3
M003, 30, Null Null 1.4请高手帮写出,即时给分,在线等,急!!!
遍历表A的各个字段值, 然后生成一个以下形式的SQL语句, 赋给@sql
SELECT b.MakingID,b.GX,
(CASE b.CaseID WHEN 'A' THEN Price ELSE NULL END) AS A,
(CASE b.CaseID WHEN 'B' THEN Price ELSE NULL END) AS B,
(CASE b.CaseID WHEN 'C' THEN Price ELSE NULL END) AS C,
(CASE b.CaseID WHEN 'D' THEN Price ELSE NULL END) AS D
FROM B
然后执行以下语句:
EXEC(@sql) 以上方法在 Windows 2000 professional, MS SQL Server2000的环境下成功验证.
A(CaseID)
a
b
c
d
...
n
然后用游标遍历,生成SQL语句.