Cross Table Query LiKE Transform in Access

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

I've one query like TRANSFORM Sum(-PV(Table1!Data/100/zzzzzzzzTable6!Field1,zzzzzzzzTable6_1!Field1*zzzzzzzzTable6!Field1,6/zzzzzzzzTable6!Field1)+100/(1+Table1!Data/100/2)^zzzzzzzzTable6_1!Field1*zzzzzzzzTable6!Field1) AS RatesToPrices SELECT tblAnalyst.AnalystID, tblAnalyst.AnalystName, zzzzzzzzTable6.Field1 FROM zzzzzzzzTable6, zzzzzzzzTable6 AS zzzzzzzzTable6_1 INNER JOIN (Table1 INNER JOIN tblAnalyst ON Table1.AnalystID=tblAnalyst.AnalystID) ON zzzzzzzzTable6_1.Instrument=Table1.Instrument WHERE (((zzzzzzzzTable6.ID)=8)) GROUP BY tblAnalyst.AnalystID, tblAnalyst.AnalystName, zzzzzzzzTable6.Field1, zzzzzzzzTable6.ID ORDER BY tblAnalyst.AnalystID, tblAnalyst.AnalystName, Table1.Instrument PIVOT Table1.Instrument;

i want Convert this one to SQL SERVER.

-- Anonymous, September 01, 2004

Answers

Andamuthu,

I see that you already have an answer regarding how to implement the PV function.

Regarding the Transform/Pivot Table functionality, the short answer is that there is no equivalent in SQL Server to the Access Transform/Pivot Table. The crosstab functionality in Access is an extension to the ANSI standard, and is one that SQL Server has not implemented.

You can deal with this by assembling the data into a temp table and then outputting the data in the format you require. This solution will require you to have preknowledge of the table dimensions. There are "solutions" out there, but they are just hacks.

Good Luck,

Eric

-- Anonymous, September 02, 2004


Moderation questions? read the FAQ