Pivot with 3 tables without pivot function

Question

How do i make pivot without using pivot function. Those data is related by parameter @semester. It can be any semester.

DECLARE @Semester VARCHAR(10)
    SET @Semester = '1'

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),
    Test VARCHAR(50),
    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,
    Test Name VARCHAR(40)
)

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

Finally, it can be either 1st look

888999 | Hazel | 15 | 10 | 10 | 20 | 45 | F | 0

OR 2nd Look

888999 | Hazel | 15 | 10 | 45 | F | 0

Naveen Kumar · Answer

Yes you can use CASE in dynamic see the below query. DECLARE @SQL NVARCHAR(MAX) = '' DECLARE @Columns NVARCHAR(MAX) = '' DECLARE @PivotColumns NVARCHAR(MAX) = '' SELECT @Columns = STRING_AGG('MAX(CASE WHEN t.TestID = ' + CAST(TestID AS VARCHAR) + ' THEN m.Marks END) AS ' + TestName ' ') FROM #Test SET @SQL = ' SELECT s.MatricNo s.Name ' + @Columns + ' s.Final1 AS Final s.Grade 0 AS Point FROM #Students s LEFT JOIN #Marks m ON s.MatricNo = m.MatricNo AND m.Semester = ' + @Semester + ' LEFT JOIN #Test t ON m.Test = t.TestID GROUP BY s.MatricNo s.Name s.Final1 s.Grade' EXEC sp_executesql @SQL

Naveen Kumar · Answer

Hi Hazel Mahmud You can use aggregation with CASE statement to get your expected outut without using PIVOT. Please check the below script. For 1st Look: DECLARE @Semester VARCHAR(10) SET @Semester = '1' SELECT s.MatricNo s.Name MAX(CASE WHEN t.TestID = 1 THEN m.Marks END) AS Assignment_1 MAX(CASE WHEN t.TestID = 2 THEN m.Marks END) AS Assignment_2 MAX(CASE WHEN t.TestID = 3 THEN m.Marks END) AS Presentation MAX(CASE WHEN t.TestID = 4 THEN m.Marks END) AS Project s.Final1 AS Final s.Grade 0 AS Point FROM #Students s LEFT JOIN #Marks m ON s.MatricNo = m.MatricNo AND m.Semester = @Semester LEFT JOIN #Test t ON m.Test = t.TestID GROUP BY s.MatricNo s.Name s.Final1 s.Grade For 2nd Look: DECLARE @Semester VARCHAR(10) SET @Semester = '1' SELECT s.MatricNo s.Name MAX(CASE WHEN t.TestID = 1 THEN m.Marks END) AS Assignment_1 MAX(CASE WHEN t.TestID = 2 THEN m.Marks END) AS Assignment_2 s.Final1 AS Final s.Grade 0 AS Point FROM #Students s LEFT JOIN #Marks m ON s.MatricNo = m.MatricNo AND m.Semester = @Semester LEFT JOIN #Test t ON m.Test = t.TestID GROUP BY s.MatricNo s.Name s.Final1 s.Grade Also the #Test create table script has sytax issue on the column Test Name correct it to Test_Name. CREATE TABLE #Test( TestID INT Test_Name VARCHAR(40) )

Hazel Mahmud · Answer

STRING_AGG is not recognize keyword in sybase . how to change ?

Hazel Mahmud · Answer

i know how to pass the value from dropdown select but can i use CASE if the testID is dynamic?..

Naveen Kumar · Answer

Create a parameterized stored procedure with input parameter as Semester and pass the value from dropdown select.

Hazel Mahmud · Answer

What if the data is from table from database.. ?.. Semester value is selected from dropdowlist value.