SQl Query Pivot table

Manish Patat

6y908SQL

This is my Query :-

SELECT
isnull(cast(year as varchar(25)),0) AS Year,
isnull(cast(January as varchar(25)),0) AS January,
isnull(cast(February as varchar(25)),0) AS February,
isnull(cast(March as varchar(25)),0) AS March,
isnull(cast(April as varchar(25)),0) AS April,
isnull(cast(May as varchar(25)),0) AS May,
isnull(cast(June as varchar(25)),0) AS June,
isnull(cast(July as varchar(25)),0) AS July,
isnull(cast(August as varchar(25)),0) AS August,
isnull(cast(September as varchar(25)),0) AS September,
isnull(cast(October as varchar(25)),0) AS October,
isnull(cast(November as varchar(25)),0) AS November,
isnull(cast(December as varchar(25)),0) AS December,
isnull(January,0)+ISNULL(February,0)+ISNULL(March,0)+ISNULL(April,0)+ISNULL(May,0)+ISNULL(June,0)+ISNULL(July,0)+ISNULL(August,0)+ISNULL(September,0)+ISNULL(October,0)+ISNULL(November,0)+ISNULL(December,0) As 'Total Weight'
FROM orderbookingdispatchmwise
PIVOT (SUM(weight) FOR month IN ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December])) AS PVTTable
ORDER BY year DESC

My Output

On this Query Output All Year And Month Data Show

But I Have Show Only Current Month And year Greater Than Data

my required Output Like..

How to Possible

Thank in advance ...

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty6y ago
Accepted answer
Hey Manish, first create the below function which will convert the month name to month number.
create function fn_month_name_to_number (@monthname varchar(25))
returns int as
begin
    declare @monthno as int;
    select @monthno = case @monthname
        when 'January' then 1
        when 'February' then 2
        when 'March' then 3
        when 'April' then 4
        when 'May' then 5
        when 'June' then 6
        when 'July' then 7
        when 'August' then 8
        when 'September' then 9
        when 'October' then 10
        when 'November' then 11
        when 'December' then 12
    end
    return @monthno
end
Then run the below query.
SELECT ISNULL(CAST([Year] AS VARCHAR(25)),0) AS [Year],
ISNULL(CAST(January as varchar(25)),0) AS January,
ISNULL(CAST(February as varchar(25)),0) AS February,
ISNULL(CAST(March as varchar(25)),0) AS March,
ISNULL(CAST(April as varchar(25)),0) AS April,
ISNULL(CAST(May as varchar(25)),0) AS May,
ISNULL(CAST(June as varchar(25)),0) AS June,
ISNULL(CAST(July as varchar(25)),0) AS July,
ISNULL(CAST(August as varchar(25)),0) AS August,
ISNULL(CAST(September as varchar(25)),0) AS September,
ISNULL(CAST(October as varchar(25)),0) AS October,
ISNULL(CAST(November as varchar(25)),0) AS November,
ISNULL(CAST(December as varchar(25)),0) AS December,
ISNULL(January,0)+ISNULL(February,0)+ISNULL(March,0)+ISNULL(April,0)+ISNULL(May,0)+ISNULL(June,0)+ISNULL(July,0)+
ISNULL(August,0)+ISNULL(September,0)+ISNULL(October,0)+ISNULL(November,0)+ISNULL(December,0) AS 'Total Weight'
FROM (SELECT * FROM Tbl2 WHERE ([Year] = YEAR(GETDATE()) AND dbo.fn_month_name_to_number (DATENAME(MONTH, GETDATE())) <= dbo.fn_month_name_to_number (Month)) OR [Year] > YEAR(GETDATE()))tbl
PIVOT
(
    SUM(Weight) FOR [Month] IN ([January], [February], [March], [April], [May], [June], [July], [August], [September], [October], [November], [December])
)
AS PVTTable  ORDER BY [Year] DESC
+2
Manish Patat6y ago
Table Structure
+1
Amit Mohanty6y ago
Hey, can you provide the table structure?
+1