I have a student list of grades for the same class over several marking periods. I want to pivot those in a query subject to create 1 line per Course. I have SQL that does this in SSMS, however when I cut and past that over to a data Query subject. I get an error:RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 14: Syntax error near "pivot". Does FM support the pivot function?
select student_id
,school_year
,building
,section_key
,course_status
,COURSE
,COURSE_SECTION
,DESCRIPTION
,M1
,M2
,M3
,M4
,M5
,M6
,DEPARTMENT
from (
select mk.student_id
,mk.section_key
,ms.DESCRIPTION
,ms.SCHOOL_YEAR
,crs.COURSE_STATUS
,ms.COURSE
,ms.COURSE_SECTION
,ms.BUILDING
,MARKING_PERIOD
,MARK_VALUE
,ms.department
from MR_STU_MARKS mk
inner join schd_ms ms on ms.SECTION_KEY=mk.SECTION_KEY
inner join schd_stu_course crs on mk.STUDENT_ID=crs.STUDENT_ID and mk.SECTION_KEY=crs.SECTION_KEY
inner join reg r on mk.STUDENT_ID=r.STUDENT_ID
where MARK_TYPE in ('6wk','SEM', 'FIN')
and ms.DEPARTMENT in ('1','3','4','5') and r.CURRENT_STATUS='a' and SCHOOL_YEAR=(select school_year from [Live].[dbo].[REG_DISTRICT]) and crs.COURSE_STATUS <> 'd'
) as m
pivot(max(mark_value) for marking_period in (M1,M2,M3,M4,M5,M6)) as pvt
In general it's a bad idea to use FM like this. Model the tables correctly, then the report developer can use a crosstab to pivot the output.
On the other hand, if you want to persist in this, you can try wrapping the SQL in braces:
{select whatever from whatever}
or setting the SQL to passthrough.
The problem is we really want this data as a single row and not to have to utilize a crosstab each time we would like to use it. Would we be better off having a stored procedure bring the data in this way and model accordingly? Or, would bringing the table in, then creating a business layer query which would aggregate the information as we want it be a better solution? We get this data from a third party and don't have the ability to change the native structure.
Have you tried setting the SQL to passthrough, or using the braces?
Yes I did...I wasn't aware that putting it in brackets utilized a pass through connection type. Thank you.