Pivot with 3 tables without pivot function

Hazel Mahmud

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

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

Naveen Kumar2y ago

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

Hazel Mahmud2y ago
i know how to pass the value from dropdown select but can i use CASE if the testID is dynamic?..
Naveen Kumar2y ago
Create a parameterized stored procedure with input parameter as Semester and pass the value from dropdown select.
Hazel Mahmud2y ago
What if the data is from table from database.. ?.. Semester value is selected from dropdowlist value.

Naveen Kumar2y ago

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)
)