Dynamic pivot query in sql server

I have a functioning pivot query for static column values which i want to convert for dynamic column values.

The query is :

with a as (

select  request_id, dateadd(month,datediff(month,0,logged_datetime),0) as 'Month'
,dateadd(month,datediff(month,0,logged_datetime),0) as 'Year'

 from requests

 )


 select  * from (

 select datepart(m,a.Month) as 'months',datepart(YEAR,a.Year) as 'years',a.request_id

  from a  ) ps

 pivot (

 count(request_id) for [years] in ([2008],[2009],[2010],[2011],[2012],[2013],[2014],[2015])
 ) as pvt
 order by months

For the dynamic query written below I am getting errors like

Msg 207, Level 16, State 1, Line 5 Invalid column name '2010'. :

select distinct datepart(year,dateadd(month,datediff(month,0,logged_datetime),0)) as 'Yearz'

into #t

FROM requests

DECLARE @Dynamic AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(Yearz)
FROM (SELECT * from #t) AS Yea


SET @Dynamic = 
  N'

 select  * from (

 select datepart(m,dateadd(month,datediff(month,0,logged_datetime),0)) as months,' + @ColumnName + ',request_id

  from requests ) ps

 pivot (

 count(request_id) for [Years] in (' + @ColumnName + ')
 ) as pvt
 order by months
'

EXEC sp_executesql @Dynamic

Answers


SAMPLE TABLE

SELECT * INTO #REQUESTS
FROM
(
    SELECT 1 request_id, '2012-06-01' logged_datetime
    UNION ALL
    SELECT 2 request_id,  '2012-05-01'
    UNION ALL
    SELECT 11 request_id, '2012-06-01' 
    UNION ALL
    SELECT 12 request_id,  '2012-05-01'
    UNION ALL
    SELECT 3 request_id,  '2012-07-01' 
    UNION ALL
    SELECT 4 request_id,  '2013-09-01' 
    UNION ALL
    SELECT 5 request_id,  '2013-10-01' 
    UNION ALL
    SELECT 6 request_id, '2014-01-01' 
    UNION ALL
    SELECT 7 request_id,  '2014-02-01' 
    UNION ALL
    SELECT 8 request_id, '2014-03-01' 
    UNION ALL
    SELECT 9 request_id,  '2014-05-01' 
    UNION ALL
    SELECT 10 request_id,  '2015-11-01' 
)TAB

QUERY

Get the column for years and order it in numeric order

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + CAST([YEAR] AS VARCHAR(4)) + ']',  '[' + CAST([YEAR] AS VARCHAR(4)) + ']')
               FROM    (SELECT DISTINCT YEAR(dateadd(month,datediff(month,0,logged_datetime),0)) [YEAR] FROM #REQUESTS) PV  
               ORDER BY CAST([YEAR] AS INT)

Now pivot the query

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             ( 
                 -- We will get the count for a year and number of request_id in that month here as CNT column
                 select DISTINCT datepart(m,a.Month) as ''months'',datepart(YEAR,a.Year) as ''years'',
                COUNT(*) OVER(PARTITION BY datepart(YEAR,a.Year),datepart(m,a.Month)) CNT
                 from
                 (
                    select  request_id, dateadd(month,datediff(month,0,logged_datetime),0) as ''Month''
                    ,dateadd(month,datediff(month,0,logged_datetime),0) as ''Year''
                     from #requests
                 )a 
             ) x
             PIVOT 
             (
                 MIN(CNT)
                 FOR years IN (' + @cols + ')
            ) p
            ORDER BY MONTHS;' 

EXEC SP_EXECUTESQL @query

I think you need to wrap your dynamic column names using the QUOTENAME function:

See example here:


Need Your Help

.toggle(“bounce”) making div disappear

javascript jquery html css toggle

Working on a very simple point and click instrument, I'm looking to have my divs bounce in place after clicked, but for some reason they are disappearing after executing their animation. The anim...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.