CODEWITHSUNDEEP
sqlsql-serverpivot-table
Megan
Megan

Reputation: 622

Pivot Rows to Columns Dynamically - SQL

/****** 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

Upvotes: 0

Views: 6474

Answers (1)

Simon1979
Simon1979

Reputation: 2108

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)

Upvotes: 1

Related Questions