Show Hide Column using dynamic Pivot query in SQL Server

Last Reply 10 months ago By smile

Posted 11 months ago

I have the following generated fees data for the months like this

AdmissionNo

Month

Fees

Head

Price

R-01

05-May

1500

0

0

R-02

05-May

1500

0

0

R-03

05-May

1500

0

0

R-01

06-Jun

1500

1

120

R-01

06-Jun

1500

2

150

R-01

06-Jun

1500

3

50

R-02

06-Jun

1500

1

120

R-02

06-Jun

1500

2

150

R-02

06-Jun

1500

3

50

R-01

07-Jul

1500

0

0

R-02

07-Jul

1500

0

0

R-03

07-Jul

1500

0

0

Now I am filtering the data based on the month and wrote the following query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(HeadName) FROM (SELECT DISTINCT HeadName FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID) AS Head
DECLARE @qry NVARCHAR(4000)
IF(@cols IS NOT NULL AND @cols <> '[0]')
    SET @qry =
              N'SELECT distinct AdmissionNo,Month,Fees, ' + @cols + '
                FROM (SELECT AdmissionNo,Month,Fees,HeadName,Price
                FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID ) p
                PIVOT (MAX(Price) FOR HeadName IN (' + @cols + ')) AS Pvt'
ELSE
BEGIN
    SET @qry = 'SELECT DISTINCT AdmissionNo,Month,Fees FROM tblFeesGenerate where Month=''05-May'''
END
EXEC sp_executesql @qry

it is not filtering the data. but when i wrote the query by applying a bit change like this it is showing nothing

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(HeadName) FROM (SELECT DISTINCT HeadName FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID) AS Head
DECLARE @qry NVARCHAR(4000)
IF(@cols IS NOT NULL AND @cols <> '[0]')
    SET @qry =
              N'SELECT distinct AdmissionNo,Month,Fees, ' + @cols + '
                FROM (SELECT AdmissionNo,Month,Fees,HeadName,Price
                FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID where Month=''05-May'') p
                PIVOT (MAX(Price) FOR HeadName IN (' + @cols + ')) AS Pvt'
ELSE
BEGIN
    SET @qry = 'SELECT DISTINCT AdmissionNo,Month,Fees FROM tblFeesGenerate '
END
EXEC sp_executesql @qry

but in where clause i wrote where Month=''06-Jun'' then it is showing data.

In simply words it is only showing data in the case of '06-Jun' whereas It must show the data based on the month of '05-May' or '07-Jul'.

https://imgur.com/f98Kg8J

Posted 10 months ago

Check the below query.

SQL

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
DECLARE @qry NVARCHAR(4000)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(Head) FROM (SELECT DISTINCT Head FROM tblFeesGenerate WHERE Head <> 0 AND Month='05-May') AS Head
IF(@cols IS NOT NULL AND @cols <> '[0]')
    SET @qry =
              N'SELECT distinct AdmissionNo,Month,Fees, ' + @cols + '
                FROM (SELECT AdmissionNo,Month,Fees,Head,Price
                FROM tblFeesGenerate
				WHERE Month=''05-May'') p
                PIVOT (MAX(Price) FOR Head IN (' + @cols + ')) AS Pvt'
ELSE
BEGIN
    SET @qry = 'SELECT DISTINCT AdmissionNo,Month,Fees FROM tblFeesGenerate WHERE Month=''05-May'''
END
EXEC sp_executesql @qry
GO
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
DECLARE @qry NVARCHAR(4000)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(Head) FROM (SELECT DISTINCT Head FROM tblFeesGenerate WHERE Head <> 0 AND Month='06-Jun') AS Head
IF(@cols IS NOT NULL AND @cols <> '[0]')
    SET @qry =
              N'SELECT distinct AdmissionNo,Month,Fees, ' + @cols + '
                FROM (SELECT AdmissionNo,Month,Fees,Head,Price
                FROM tblFeesGenerate
				WHERE Month=''06-Jun'') p
                PIVOT (MAX(Price) FOR Head IN (' + @cols + ')) AS Pvt'
ELSE
BEGIN
    SET @qry = 'SELECT DISTINCT AdmissionNo,Month,Fees FROM tblFeesGenerate WHERE Month=''06-Jun'''
END
EXEC sp_executesql @qry
GO
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
DECLARE @qry NVARCHAR(4000)
SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(Head) FROM (SELECT DISTINCT Head FROM tblFeesGenerate WHERE Head <> 0 AND Month='07-Jul') AS Head
IF(@cols IS NOT NULL AND @cols <> '[0]')
    SET @qry =
              N'SELECT distinct AdmissionNo,Month,Fees, ' + @cols + '
                FROM (SELECT AdmissionNo,Month,Fees,Head,Price
                FROM tblFeesGenerate
				WHERE Month=''07-Jul'') p
                PIVOT (MAX(Price) FOR Head IN (' + @cols + ')) AS Pvt'
ELSE
BEGIN
    SET @qry = 'SELECT DISTINCT AdmissionNo,Month,Fees FROM tblFeesGenerate WHERE Month=''07-Jul'''
END
EXEC sp_executesql @qry

Output


Posted 10 months ago

Finally,I got the desired output by putting single quote in where clause instead of 0. Here is the syntax. 

SELECT @cols= ISNULL(@cols + ',','') + QUOTENAME(HeadName) FROM (SELECT DISTINCT HeadName FROM tblFeesGenerate as fg inner join tblFeesHead as fh on fg.HeadID=fh.HeadID WHERE HeadName <> '' AND Month='05-May') AS Head

Now pls let me check more after that i will let u know. Thanks