Show Hide Column using dynamic Pivot query in SQL Server

smile

SOLVED CLOSED ACTIVE ANSWERED
User: smile
Posted: on May 02, 2019 11:49 PM
Forum: SQL Server
Answer: 2
Views: 5071

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'.

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 13

Reply
ANSWER
User: dharmendr
Replied: on May 09, 2019 02:20 AM
Report

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

smile

0 15

Reply
ANSWER
User: smile
Replied: on May 09, 2019 04:19 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Show Hide Column using dynamic Pivot query in SQL Server

Company

Explore

Follow us

Contact Us