locked
Dynamically Select Month and Year From A Date Using Pivot RRS feed

  • Question

  • User1152553138 posted
    SELECT OrderNo, isnull([January],0) as Jan, isnull([February],0) as Feb, isnull([March],0) As Mar, isnull([April],0) As Apr, isnull([May],0) As May
    FROM 
    (SELECT OrderNo,DATENAME(MONTH, Date) as [Month], [Qty] 
    FROM MonthlyPivot) p
    PIVOT
    (
    sum ([Qty])
    FOR Month IN
    ( [January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
    ) AS pvt

    How to select month and year dynamically using pivot. like Jan-2018,Feb-2018,Mar-2018.

    The above is showing the column as only Jan,Feb,March

    Wednesday, January 31, 2018 5:18 AM

All replies

  • User77042963 posted
    SELECT OrderNo
    , isnull([January],0) as [Jan-2018]  
    , isnull([February],0) as [Feb-2018]
    , isnull([March],0) As [Mar-2018]
    , isnull([April],0) As [Apr-2018]
    , isnull([May],0) As [May-2018]
    FROM 
    (SELECT OrderNo,DATENAME(MONTH, Date) as [Month], [Qty] 
    FROM MonthlyPivot
    
    WHERE Year(Date)=2018
    ) p
    PIVOT
    (
    sum ([Qty])
    FOR Month IN
    ( [January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
    ) AS pvt

    Wednesday, January 31, 2018 2:32 PM
  • User1152553138 posted

    Thanks 

    Can we get this dynamically Jan-2018,Feb-2018,Mar-2018

    Team Nov-17 Dec-17 Jan-18
    Team1 250 350 500
    Team2 100 122 222
    Not Like
    Team Nov Dec Jan
    Team1 250 350 500
    Team2 100 122 222
    Thursday, February 1, 2018 5:52 AM
  • User1152553138 posted
    declare @cols varchar(max);
    select @cols = STUFF((SELECT ',' +
                            REPLACE(RIGHT(CONVERT(VARCHAR(11), Datecolumn, 106), 8), ' ', '-') 
                          FROM [Table]             
        GROUP BY REPLACE(RIGHT(CONVERT(VARCHAR(11), Datecolumn, 106), 8), ' ', '-') 
         , DATEPART(MONTH, CONVERT(DATETIME, Datecolumn))
        ORDER BY DATEPART(MONTH, CONVERT(DATETIME, Datecolumn))        
                          FOR XML PATH(''), TYPE
                         ).value('.', 'NVARCHAR(MAX)') 
                            , 1, 1, '');
                            
    select @cols;
    Go  

    Output

    (No column name)
    Apr-2018,May-2018,Jun-2018,Jul-2018,Aug-2018,Sep-2018

    The above shows all the month as 1 column but need each month as separate column.

    Friday, February 2, 2018 12:17 PM
  • User77042963 posted

    Search for dynamic pivot sample code.

    Friday, February 2, 2018 3:02 PM