AnsweredAssumed Answered

Crosstab using ExecuteSQL

Question asked by RicardoVin on Sep 1, 2012
Latest reply on Sep 12, 2012 by RicardoVin

Title

Crosstab using ExecuteSQL

Post

Hello,

I have a table like this:

Exam|Date |Result

---------------------------------------

Exam101-01-2012 10

Exam201-01-201212

Exam101-02-201211

Exam201-03-201214

XYZ

 

I can have many kinds of exams performed during a long period.

I need to show this data following the layout below

 

Exam|Jan-01-2012   |Jan-02-2012   |Y. . . (as many as in the table)

-----|-------------------------------------------------

Exam1|1011-

Exam2|1214-

X|--Z

.

.

.

(as many as in the table)

 

In MS Access it is very easy to do that using the SQL statement

-----------------------------------------

TRANSFORM First(Table_Exam.Result) AS FirstOfResult

SELECT Table_Exam.Exam

FROM Table_Exam

GROUP BY Table_Exam.Exam

PIVOT Format(Date,"mmm/dd/yyyy")

-----------------------------------------

However, this SQL statement doe not works in Filemaker using ExecuteSQL.

All solutions I could find works only for a defined number of columns, but in my case the columns change every day as well as the kind of exam .

Does anybody knows how to get it working

 

Thank you.

Outcomes