im trying return a matrix from my tables. My two tables look like this:
**Employe_Course_Instance**
PersonNumber Year Period CourseCode Hour
111 2015 3 a1 10
222 2016 4 a2 4
333 2018 2 a3 8
444 2015 1 a4 5
**Employe**
PersonNumber FirstName LastName Salary Type
111 A LA 100 teacher
222 B LB 120 teacher
333 C LC 150 teacher
444 D LD 120 teacher
I need a table looks like this: where CourseCode and period will be data in table like CourseCode/Period and can only contain 4 periods. Salary to be displayed in rows in CourseCode/Period.
FirstName LastName a1/3 a2/4 a3/2 a4/1 Hours
A LA 100 10
B LB 120 4
C LC 150 8
D LD 120 5
and this is how i try to do this->
declare @ColumnNames nvarchar(max)
declare @SQL nvarchar(max)
set @SQL = ''
set @ColumnNames = ''
select @ColumnNames = @ColumnNames + quotename (coursecode) + ','
from bemaning
set @ColumnNames = left (@ColumnNames, len (@ColumnNames) - 1)
print @ColumnNames
set @SQL =
'select * from
(select
employe.firsname, employe.lastname,
Employe_Course_Instance.coursecode + '' / '' + CAST(Employe_Course_Instance.period as varchar (50)) as CourseCode_Period,
Employe_Course_Instance.year, Employe_Course_Instance.hours
from
employe inner join Employe_Course_Instance on employe.personnumber = Employe_Course_Instance.personnumber)
pivot(
sum(hour)
for CourseCode_Period in (' + @ColumnNames +')
) as pivot_table'
--print @SQL
execute sp_executesql @SQL
The error i get->
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'pivot'.
