tblStdReg:
RegNo,Referenceno,SName,FName
R-001,101,abc,sky
R-002,102,sky,abc
tblAddFees:
Head Amount ClassID
Admission Fees 500 19
TutionFees 1000 19
Required Output
Class, AdmissionNo, ReferenceNo, SName,FName,AdmissionFees,TutionFees, TotalAmount
19, R-001, 101, abc, sky, 500, 1000, 1500
19, R-002, 102, sky, abc, 500, 1000, 1500
my query is
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME([Head])
FROM (SELECT DISTINCT Head FROM tblAddFees) AS Course
DECLARE @qry NVARCHAR(4000)
SET @qry =
N'
;WITH cteStudentMarksDetails
AS ( SELECT TA.ClassID
,(SELECT AdmissionNo FROM tblStdReg TS WHERE TS.ClassID = TA.ClassID) as AdmissionNo
,(SELECT SName FROM tblStdReg TS WHERE TS.ClassID = TA.ClassID) as SName
,(SELECT FName FROM tblStdReg TS WHERE TS.ClassID = TA.ClassID) as FName
,SUM(Amount) TotalAmount
FROM tblAddFees TA
GROUP BY ClassID
)
SELECT ClassID,AdmissionNo
,SName
,FName
, ' + @cols + '
,TotalAmount
FROM (SELECT ClassID
,Head
,Amount
,(SELECT TotalAmount FROM cteStudentMarksDetails csd Where csd.ClassID = sm.ClassID) as TotalAmount
,(SELECT AdmissionNo FROM tblStdReg TS WHERE TS.ClassID = sm.ClassID) as AdmissionNo
,(SELECT SName FROM cteStudentMarksDetails csd Where csd.ClassID = sm.ClassID) as SName
,(SELECT FName FROM cteStudentMarksDetails csd Where csd.ClassID = sm.ClassID) as FName
FROM tblAddFees as sm
) p
PIVOT (MAX(Amount) FOR Head IN ('+@cols+')) AS Pvt'
EXEC sp_executesql @qry
Showing error is that
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.