# Dynamically Select Month and Year From A Date Using Pivot

• ### 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