Dynamic columns using PIVOT and I need to use those dynamic columns along with aggregate function to generate reports month wise -SQL Server 2012

Question

I'm using SQL SERVER 2012 and i am using the below query

SELECT Status AS [Status]
    ,[Feb-2016] AS [Current(Feb)]
    ,[Jan-2016]
    ,[Dec-2015]
    ,[Nov-2015]
    ,[Oct-2015]
    ,[Sep-2015]
    ,[Aug-2015]
    ,[Jul-2015]
    ,[Jun-2015]
    ,[May-2015]
    ,[Apr-2015]
    ,[Mar-2015]
    ,[Feb-2015]
INTO #TempTable
FROM (
    SELECT Status
        ,[Count]
        ,[Month]
    FROM CTE2--I'm using this Common Table Expression in the SP
    ) AS [Mnth]
PIVOT(SUM([Count]) FOR [Month] IN (
            [Feb-2016]
            ,[Jan-2016]
            ,[Dec-2015]
            ,[Nov-2015]
            ,[Oct-2015]
            ,[Sep-2015]
            ,[Aug-2015]
            ,[Jul-2015]
            ,[Jun-2015]
            ,[May-2015]
            ,[Apr-2015]
            ,[Mar-2015]
            ,[Feb-2015]
            )) AS [nNamePivot]

For the above part i can use dynamic Query

If i use dynamic query, how to get output for the below part and I need to use those dynamic columns along with aggregate function to generate reports month wise.

SELECT *
FROM #TempTable
UNION
SELECT 'Total'
    ,SUM([Current(Feb)])
    ,SUM([Jan-2016])
    ,SUM([Dec-2015])
    ,SUM([Nov-2015])
    ,SUM([Oct-2015])
    ,SUM([Sep-2015])
    ,SUM([Aug-2015])
    ,SUM([Jul-2015])
    ,SUM([Jun-2015])
    ,SUM([May-2015])
    ,SUM([Apr-2015])
    ,SUM([Mar-2015])
    ,SUM([Feb-2015])
FROM #TempTable

Please help me with this.

Have you tried this using CASE for Pivoting ? http://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when — Aditya, Feb 18 '16 at 05:32
Please edit it, make it more readable , if you want the answer. — surajs1n, Feb 18 '16 at 05:36
@Aditya Yes. I tried. But it doesn't fulfill my requirement. — Kiran Reddy, Feb 18 '16 at 05:55
Can you post the data\screenshot here ? like SELECT * FROM #TEMP & the desired data format ? — Aditya, Feb 18 '16 at 06:40

Khalid Amin · Accepted Answer · 2016-02-18T05:42:16.897

0

Another dynamic string can be created for the aggregations in the same way as you create one for column names. For example, the string for column names will look like:

[Jan-2015], [Feb-2015], [Mar-2015], [Apr-2015]

and the string for aggregations will look like:

SUM([Jan-2015]), SUM([Feb-2015]), SUM([Mar-2015]), SUM([Apr-2015])

To create dynamic string of column names:

STUFF((SELECT ',['+col_name+']' FROM table FOR XML PATH('')),1,1,'');

and to create a string for aggregations:

STUFF((SELECT ',SUM(['+col_name+'])' FROM table FOR XML PATH('')),1,1,'');

edited Feb 18 '16 at 05:42

answered Feb 18 '16 at 05:36

Khalid Amin

872
3
12
26

Thanks brother.:) Any better way? – Kiran Reddy Feb 18 '16 at 06:05
That's the only way I know. Because the columns are dynamic, we have to apply any aggregations dynamically – Khalid Amin Feb 18 '16 at 06:13
I need to calculate individually. If it's from jan 2016 to dec 2016 then it should be like sum(jan-2016),sum(feb-2016),....,sum(dec-2016) Note: Months will change depending on the report parameters. – Kiran Reddy Feb 18 '16 at 06:17
Yes, the STUFF statement will give you a comma separated string with individual column names, and you can use this string in your PIVOT function. If the columns are coming from parameters, you will need to add a WHERE clause to the STUFF query. – Khalid Amin Feb 18 '16 at 06:30
Note that manually enclosing database object names is not the way to go. You should use the [`QUOTENAME`](https://msdn.microsoft.com/nl-be/library/ms176114.aspx) function to properly quote these object names. Consider a column name like `contrib[i,t]` and watch this way of working fail. – TT. Feb 18 '16 at 08:33
@TT.Yes indeed, I wanted to keep it as simple as possible – Khalid Amin Feb 18 '16 at 22:49

Dynamic columns using PIVOT and I need to use those dynamic columns along with aggregate function to generate reports month wise -SQL Server 2012

1 Answers1