Crosstab queries ( From Access to SQL Server 2000)

[vinodi]

Dear all,

I have a full fledged running application developed in VB 6.0 working with Access 2000. Now our client wants to migrate the data to SQL Server 2000. Therefore there are some changes to be done in the VB application. Everything is allright upto this point.

Now my application conatins certain SQL statements for creating crosstab queries. For example

Sql = "TRANSFORM last(Tmp_Data_Flag.Flag) SELECT grid_name, feeder_name, voltage_level, [tmp_ds_list.Meter_no] as meter_no, count(Tmp_Data_Flag.Flag) AS [Total] FROM Tmp_Data_Flag RIGHT JOIN tmp_ds_list ON [tmp_Data_Flag].[meter_no]=[tmp_ds_list].[meter_no] GROUP BY grid_name, feeder_name, voltage_level, [tmp_ds_list.Meter_no] PIVOT Format([schedule_date], 'dd/MM/yyyy')"

Since TRANSFORM, the last() function and PIVOT statements are not supported in SQL Server what is the alternative available for creating a crosstab query.

Any suggestions in terms of steps to be performed.

This input will be highly appreciated.

With warm regards,

Vinodi


~~The only place where SUCCESS comes before WORK is in the dictionary~~

 

[BobJacksonNCI]

I'm not quite an SQL Novice...

But searching SQL Server books online for Cross-Tab Report results in a Northwind example of using
Select ... Sum(.... Case...

To create a temporary table in pivot form.



HTH,
Bob