Pivot Rows to Columns Dynamically - SQL

Question

/****** Script for SelectTopNRows command from SSMS  ******/

declare @ActivityYear int = 2014
declare @ActivityYear1 int = 2015
declare @ActivityMonth int = 1
declare @ActivityMonth1 int = 3


Select FinancialCategory, ID, (CONVERT(varchar(5), ActivityMonth) + '-' 
   + CONVERT(varchar(5), ActivityYear)) As [Month-Year], Sum(HoursCharged) As [Hours]
FROM Forecast
where (ActivityMonth between @ActivityMonth and @ActivityMonth1)
            AND (ActivityYear between @ActivityYear and @ActivityYear1)
            AND FinancialCategory = 'Forecast'

Group By FinancialCategory, ID,ActivityMonth, ActivityYear

This Outputs a table that looks like this: enter image description here

And I would like to transpose it to have the hours for each ID broken out by the dates in the range. Note: this range of dates will be dynamic, I set initial dates for testing purposes. enter image description here

Community · Accepted Answer · 2017-05-23 12:24:33Z

I learnt a bit about dynamic pivot recently, this post helped a lot. As a practice I converted yours, which I think would look like this, but isn't tested as I haven't time tcreate tables etc at the moment. HTH.

declare @ActivityYear int = 2014
declare @ActivityYear1 int = 2015
declare @ActivityMonth int = 1
declare @ActivityMonth1 int = 3

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)       

select @cols = STUFF((SELECT distinct ',' + QUOTENAME((CONVERT(varchar(5), ActivityMonth) + '-' 
                + CONVERT(varchar(5), ActivityYear)))
            FROM Forecast
            WHERE (ActivityMonth between @ActivityMonth and @ActivityMonth1)
                AND (ActivityYear between @ActivityYear and @ActivityYear1)
                AND FinancialCategory = 'Forecast'
        FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT FinancialCategory, ID, ' + @cols + ' FROM
             (
                SELECT FinancialCategory, ID, (CONVERT(varchar(5), ActivityMonth) + ''-'' 
                    + CONVERT(varchar(5), ActivityYear)) As [Month-Year],HoursCharged
                FROM Forecast
                WHERE (ActivityMonth between ' + @ActivityMonth + ' and ' + @ActivityMonth1 + ')
                            AND (ActivityYear between ' + @ActivityYear + ' and ' +  
                            @ActivityYear1 + ')
                            AND FinancialCategory = ''Forecast'' 
            ) x
            PIVOT 
            (
                Sum(HoursCharged)
                for (CONVERT(varchar(5), ActivityMonth) + ''-''
                    + CONVERT(varchar(5), ActivityYear)) in (' + @cols + ')
            ) p '

execute(@query)

I am receiving the error: Must declare the scalar variable "@ActivityMonth". I will look into this more tomorrow morning.
ah I see that fixed that error now I am getting: Conversion failed when converting the nvarchar value ' SELECT FinancialCategory, SID, [1-2014],[1-2015],[2-2014],[2-2015],[3-2014],[3-2015] FROM ( SELECT FinancialCategory, SID, ActivityMonth, ActivityYear, HoursCharged FROM Forecast WHERE (ActivityMonth between ' to data type int.
i found the solution! i removed the where clause in '@query', then I got a syntax error so I changed the SELECT statement in '@query' to include the conversions...I will edit your solution with mine...Thanks!

Collectives™ on Stack Overflow

Pivot Rows to Columns Dynamically - SQL

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related