Hi smile,
Refer the below sample query.
SQL
CREATE TABLE Pvt1(FeeDetailID INT,FeeCatName CHAR(25),FeesHead CHAR(25),Amount INT)
INSERT INTO Pvt1 VALUES(1,'One Class','Admission Fees',500)
INSERT INTO Pvt1 VALUES(2,'One Class','Tution Fees',1000)
INSERT INTO Pvt1 VALUES(3,'Two Class','Admission Fees',700)
INSERT INTO Pvt1 VALUES(4,'Two Class','Tution Fees',1200)
INSERT INTO Pvt1 VALUES(5,'Two Class','Exam Fees',300)
SELECT * FROM Pvt1
-- For FeeCatName = One Class
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME(FeesHead) FROM (SELECT DISTINCT FeesHead FROM Pvt1) AS FeesHead
SET @DynamicPivotQuery = ';WITH CTE AS(SELECT FeesHead,FeeCatName,Amount FROM Pvt1)
SELECT FeeCatName,'+@ColumnName+' FROM CTE
PIVOT (MAX(Amount)
FOR FeesHead IN('+@ColumnName+')) p
WHERE FeeCatName = ''One Class''
ORDER BY FeeCatName DESC'
EXEC(@DynamicPivotQuery)
-- For FeeCatName = Two Class
DECLARE @DynamicPivotQuery1 AS NVARCHAR(MAX)
DECLARE @ColumnName1 AS NVARCHAR(MAX)
SELECT @ColumnName1 = ISNULL(@ColumnName1 + ',','')+ QUOTENAME(FeesHead) FROM (SELECT DISTINCT FeesHead FROM Pvt1) AS FeesHead
SET @DynamicPivotQuery1 = ';WITH CTE AS(SELECT FeesHead,FeeCatName,Amount FROM Pvt1)
SELECT FeeCatName,'+@ColumnName1+' FROM CTE
PIVOT (MAX(Amount) FOR FeesHead IN('+@ColumnName1+')) p
WHERE FeeCatName = ''Two Class''
ORDER BY FeeCatName DESC'
EXEC(@DynamicPivotQuery1)
-- Without FeeCatName
DECLARE @DynamicPivotQuery2 AS NVARCHAR(MAX)
DECLARE @ColumnName2 AS NVARCHAR(MAX)
SELECT @ColumnName2 = ISNULL(@ColumnName2 + ',','')+ QUOTENAME(FeesHead) FROM (SELECT DISTINCT FeesHead FROM Pvt1) AS FeesHead
SET @DynamicPivotQuery2 = ';WITH CTE AS(SELECT FeesHead,FeeCatName,Amount FROM Pvt1)
SELECT FeeCatName,'+@ColumnName2+' FROM CTE
PIVOT (MAX(Amount)
FOR FeesHead IN('+@ColumnName2+')) p
ORDER BY FeeCatName DESC'
EXEC(@DynamicPivotQuery2)
Screenshot
