Let's Twist Again, Like we did with PIVOT...

by Peter Wone - GUI Computing
Image of Line Break

The TRANSFORM statement is probably unique to Microsoft Access. Probably you know it as the crosstab query. Check out the following:

  TRANSFORM Sum([Quarterly Sales Figures (table)].Sales)
  SELECT Format([Date],"yyyy") AS Year
  FROM [Quarterly Sales Figures (table)]
  GROUP BY Format([Date],"yyyy")
  PIVOT "Quarter " & Format([Date],"q");

Thatís dead easy in Jet SQL. Check it out without PIVOT: what a mess!

  SELECT DISTINCT Format([Date], "yyyy") AS Year, 
    (SELECT Sales FROM [Quarterly Sales Figures (table)] 
        WHERE Format([Date], "q") = "1"
			AND Format(T1.[Date], "yyyy") = Format([Date], "yyyy"))
     AS Q1,
    (SELECT Sales FROM [Quarterly Sales Figures (table)]
        WHERE Format([Date], "q") = "2"
              AND Format(T1.[Date], "yyyy") = Format([Date], "yyyy"))
     AS Q2,
    (SELECT Sales FROM [Quarterly Sales Figures (table)]
        WHERE Format([Date], "q") = "3"
              AND Format(T1.[Date], "yyyy") = Format([Date], "yyyy"))
     AS Q3,
    Format([Date], "q") = "4"
     AND Format(T1.[Date], "yyyy") = Format([Date], "yyyy"))
     AS Q4
    FROM [Quarterly Sales Figures (table)] AS T1;

Worse, what if, unlike this (thoroughly cooked) example, the number of categories isnít fixed? In that case you have to use an N-way self-join. I canít be bothered writing up the N-way self-join solution (as I canít remember offhand how to do it).

Come on guys, Iím not the only bloke in the country with a knowledge of SQL. Write an article on N-way self-joins and get your name up in lights. Or in print, at any rate. And save me hours of research.

If youíre using Jet as middleware for VB/SQL Server (or VB/Oracle etc.) you can use your view or stored procedure to do the restriction and then let Jet do its funky thang. This is quite effective. The crux of efficient client/server strategy is to perform all restriction at the server. This limits traffic, which is the real killer.

I use this strategy all the time to work around the fact that SQL Server wonít let me have an ORDER BY or GROUP BY clause in a view. I could use a stored procedure, but then I wouldnít be able to attach it to a Jet database in any way other than using a passthrough query like EXEC sp_name.

And thatís no fun ó I canít use all Jetís goodies to play with the result unless I wrap yet another query around it. Nor can I have a look at field definitions. Blurk.


Written by: Peter Wone
August 1995

Image of arrow to previous article Image of arrow to next article

[HOME] [TABLE OF CONTENTS] [SEARCH]