CREATE TABLE myTestData(
[Year] int,
CRS_Code varchar(10),
School varchar(10),
Sub varchar(10),
prcnt money)
INSERT INTO myTestData
SELECT 2011, 'KT101', 'ML', 'English', .10 UNION ALL
SELECT 2011, 'KT101', 'ML', 'French', .60 UNION ALL
SELECT 2011, 'KT101', 'ML', 'German', .30 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'English', .60 UNION ALL
SELECT 2011, 'KT101', 'LAW', 'French', .40 UNION ALL
SELECT 2011, 'KT102', 'ML', 'French', .50 UNION ALL
SELECT 2012, 'KT102', 'ML', 'French', .80 UNION ALL
SELECT 2013, 'KT102', 'ML', 'Japanese', .80 UNION ALL
SELECT 2013, 'KT102', 'JK', 'English', .20
DECLARE @_SQL varchar(8000), @int int
SELECT @_SQL = 'SELECT [Year], CRS_Code', @int = 0
SELECT @_SQL = @_SQL + ', MAX(CASE WHEN t.School = ''' + tabs.School + ''' and t.SUB = ''' +
tabs.Sub + ''' THEN t.prcnt else 0 END) as ['+tabs.School+'-'+tabs.Sub +']' +char(13)
FROM (Select School, SUB FROM (SELECT DISTINCT SUB FROM myTestData) as a
CROSS JOIN (SELECT DISTINCT School FROM myTestData) as b) as Tabs
SELECT @_SQL = @_SQL + 'FROM myTestData t GROUP BY [YEAR], CRS_Code'
PRINT @_SQL
Exec(@_SQL)