row to column

[Hazeleena]

i have below sql -- Declare and set the semester variable DECLARE @Semester VARCHAR(10) SET @Semester = '2'

-- Create the temporary tables CREATE TABLE #Students ( MatricNo INT PRIMARY KEY, Name VARCHAR(100), Final1 INT, Grade VARCHAR(2) )

INSERT INTO #Students VALUES(888999, 'Hazel', 221, 'F')

CREATE TABLE #Marks ( MatricNo INT, Semester VARCHAR(10), TestID INT, Marks INT )

INSERT INTO #Marks VALUES(888999, '1', 1, 15) INSERT INTO #Marks VALUES(888999, '1', 2, 10) INSERT INTO #Marks VALUES(888999, '1', 3, 10) INSERT INTO #Marks VALUES(888999, '1', 4, 20) INSERT INTO #Marks VALUES(888999, '2', 1, 25) INSERT INTO #Marks VALUES(888999, '2', 2, 20) INSERT INTO #Marks VALUES(888999, '2', 3, 30)

CREATE TABLE #Test ( TestID INT,
TestName VARCHAR(50) )

INSERT INTO #Test VALUES(1, 'Assignment_1') INSERT INTO #Test VALUES(2, 'Assignment_2') INSERT INTO #Test VALUES(3, 'Presentation') INSERT INTO #Test VALUES(4, 'Project')

CREATE TABLE #TestNameMarkTemp ( matrik INT, name VARCHAR(100), test_name VARCHAR(100), Markah INT, Final INT, Grade VARCHAR(2) )

-- Populate #TestNameMarkTemp with data for the specified semester INSERT INTO #TestNameMarkTemp (matrik, name, test_name, Markah, Final, Grade) SELECT DISTINCT m.MatricNo, s.Name, t.TestName, m.Marks, s.Final1, s.Grade FROM #Marks m INNER JOIN #Students s ON m.MatricNo = s.MatricNo INNER JOIN #Test t ON m.TestID = t.TestID
WHERE m.Semester = @Semester

SELECT * from #TestNameMarkTemp

how do i make the test_name & Markah as column... test_name & column is dynamic row retrieve from database table.

Plese DO NOT give example as below as below example is for static row

SELECT @ID = ID, @Value1 = MAX(CASE WHEN ColumnName = 'Column1' THEN ColumnValue END), @Value2 = MAX(CASE WHEN ColumnName = 'Column2' THEN ColumnValue END) FROM #TempTable GROUP BY ID