How can I display like
studentId Apr May Jun <br />
xyz sum of apr sum of may sum of Jun<br />
<br />
like wise for all month<br />
<br />
<br />
There are two table available FeeAssigned and FeeExemption.
CREATE TABLE FeeAssigned ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))
CREATE TABLE FeeExemption ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))
INSERT INTO FeeAssigned
SELECT 'TUITION FEE',120,120,120,120,120,120,120,120,120,120,120,120,'STUD01'
UNION ALL
SELECT 'TRAN FEE',50,50,50,50,50,50,50,50,50,50,50,50,'STUD01'
INSERT INTO FeeExemption
SELECT 'TUITION FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'
UNION ALL
SELECT 'TRAN FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'
What I have tried:
<br />
CREATE PROC GetFeeStructureForStud(@StudentId NVARCHAR(50))<br />
AS<br />
BEGIN<br />
SELECT FA.StudentId,FA.[FEE HEAD],<br />
FA.APR - ISNULL(FET.APR,0) AS APR,<br />
FA.MAY - ISNULL(FET.MAY,0) AS MAY,<br />
FA.JUN - ISNULL(FET.JUN,0) AS JUN,<br />
FA.JUL - ISNULL(FET.JUL,0) AS JUL,<br />
FA.AUG - ISNULL(FET.AUG,0) AS AUG,<br />
FA.SEP - ISNULL(FET.SEP,0) AS SEP,<br />
FA.OCT - ISNULL(FET.OCT,0) AS OCT,<br />
FA.NOV - ISNULL(FET.NOV,0) AS NOV,<br />
FA.DEC - ISNULL(FET.DEC,0) AS DEC,<br />
FA.JAN - ISNULL(FET.JAN,0) AS JAN,<br />
FA.FEB - ISNULL(FET.FEB,0) AS FEB,<br />
FA.MAR - ISNULL(FET.MAR,0) AS MAR<br />
FROM FeeAssigned FA LEFT JOIN <br />
(SELECT FE.StudentId,FE.[FEE HEAD],<br />
SUM(FE.APR) AS APR,<br />
SUM(FE.MAY) AS MAY,<br />
SUM(FE.JUN) AS JUN,<br />
SUM(FE.JUL) AS JUL,<br />
SUM(FE.AUG) AS AUG,<br />
SUM(FE.SEP) AS SEP,<br />
SUM(FE.OCT) AS OCT,<br />
SUM(FE.NOV) AS NOV,<br />
SUM(FE.DEC) AS DEC,<br />
SUM(FE.JAN) AS JAN,<br />
SUM(FE.FEB) AS FEB,<br />
SUM(FE.MAR) AS MAR <br />
FROM FeeExemption FE <br />
GROUP BY FE.StudentId,FE.[FEE HEAD]) AS FET ON (FA.StudentId=FET.StudentId) AND (FA.[FEE HEAD]=FET.[FEE HEAD])<br />
WHERE FA.StudentId=@StudentId<br />
END <br />
<br />