MS Access Crosstab Query to Standard SQL

[jmah08]

I am in the process of converting an MS Access database into Oracle. I hit a bump as I was converting queries. I have several corsstab queries within access that I would like to convert into standard SQL for our developers, but am not sure of the command. Below is the MS Access SQL cut and pasted right from the query (the field names etc. are generic for the example). How would I re-write this in standard SQL to use in something like Sequel Navigator?

TRANSFORM Count(Table.Field1) AS [Count]
SELECT Table.Field2, Table.Field3, Table.Field4
FROM Table
WHERE (((Table.Field5) Is Not Null))
GROUP BY Table.Field2, Table.Field3, Table.Field4
PIVOT Table.Field6;


Thanks

[antares686]

Unfortunately not as simple. Pivot is key to Access and the JET database language. To do roughly the same thing in Oracle is not hard just different. Not knowin what all the possible values of Field6 thou I cannot give you an example based on your data. But you will need to use the DECODE and SUM functions with your select and Group BY. Something like this most likely.


SELECT
Field2,
Field3,
Field4,
SUM(DECODE(Field6,Val1,Count(Field1),0)) AS Val1,
SUM(DECODE(Field6,Val2,Count(Field1),0)) AS Val2,
SUM(DECODE(Field6,Val3,Count(Field1),0)) AS Val3
FROM
[Table]
GROUP BY
Field2,
Field3,
Field4
WHERE
Field5 IS NOT NULL



Basically the syntax for DECODE is this.

DECODE(field, condition, do, [condition, do, condition, do,...,] else)