Pivot with 3 tables in Sybase
I have below table & below pivot query in sybase stored procedure.. Sybase does not recognize keyword 'QUOTENAME' AND 'REPLACE'. how do i change the query . TQ very much.CREATE TABLE #Students (
MatricNo VARCHAR(20) PRIMARY KEY,
Name VARCHAR(100),
Final1 INT,
Grade varchar(2)
)
INSERT INTO #Students VALUES(888999, 'Hazel', 221, 'F')
CREATE TABLE #Marks(
MatricNo VARCHAR(20),
Semester INT,
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,
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')
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',', '') + QUOTENAME(TestName) FROM (SELECT DISTINCT [TestName] FROM #Test t INNER JOIN #Marks m ON m.TestID =t.TestID WHERE Semester = @Semester) AS [TestName]
DECLARE @ColumnForSum AS NVARCHAR(MAX)
SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
SET @DynamicPivotQuery = 'SELECT MatricNo, Name, '+@ColumnName+', SUM('+@ColumnForSum+') Final,Final1, Grade
FROM
(
SELECT m.MatricNo, s.Name, s.Final1, s.Grade,m.TestID, m.Marks,t.TestName
FROM #Students s
INNER JOIN #Marks m ON s.MatricNo = m.MatricNo
INNER JOIN #Test t ON m.TestID = t.TestID
WHERE Semester = '+@Semester+'
)t
PIVOT
(
MAX([Marks]) FOR TestName IN ('+@ColumnName+')
)piv GROUP BY MatricNo,Name,'+@ColumnName+',Final1, Grade'
EXEC (@DynamicPivotQuery)
DROP TABLE #Students
DROP TABLE #Marks
DROP TABLE #Test