Sql-server – Dynamic pivot: sum of sales per month

dynamic-sqlpivotsql server

My table is like this:

Sitecode    Month  Amount
--------    -----  ------
XX          Jan     1000
XX          Jan     3000
XX          Apr     3000
XX          Apr     1000

What I want is, to show the result something like this:

Sitecode    MonthJAN   MonthAPR
--------    --------   --------
XX          4000       4000

Best Answer

As others have said this is known as a PIVOT. There are several ways in which you can transform your data from rows into columns of data.

If you know the values ahead of time, then you can hard-code the values. Prior to the PIVOT function you would use an aggregate function with a CASE statement.

Aggregate/CASE Version:

select sitecode,
  sum(case when [month] = 'Jan' then amount else 0 end) MonthJan,
  sum(case when [month] = 'Apr' then amount else 0 end) MonthApr
from yourtable
group by sitecode;

See SQL Fiddle with Demo.

The PIVOT function was made available in SQL Server 2005, so if you are using that version or a newer one then you can apply that to your data.

Static PIVOT:

select *
from
(
  select sitecode,
    [month],
    amount
  from yourtable
) src
pivot
(
  sum(amount)
  for month in (Jan, Apr)
) piv;

See SQL Fiddle with Demo

The above two versions work great if you know the values ahead of time. If not, then you will use dynamic sql to create the result.

Dynamic PIVOT:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT sitecode,' + @cols + ' from 
             (
                select sitecode, [month], amount
                from yourtable
            ) x
            pivot 
            (
                sum(amount)
                for month in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

All 3 versions of this will return the same result:

| SITECODE |  JAN |  APR |
--------------------------
|       XX | 4000 | 4000 |

Related Solutions

Sql-server – Dynamic Number of Columns in Pivot Table

Working from Aaron Bertrand's Script to create dynamic PIVOT queries in SQL Server and with a sample data set defined thus:

if object_id('#ObtainedMarkEntry') is not null drop table #ObtainedMarkEntry;
create table #ObtainedMarkEntry(
    StudentID       int not null, 
    SubjectID       int not null, 
    ObtainedMarks       int not null
);
insert #ObtainedMarkEntry(StudentID, SubjectID, ObtainedMarks)
values
        (1,100,90) ,(1,101,70)
       ,(2,100,65) ,(2,300,55)
;

then this SQL:

declare @columnsSrc nvarchar(max) = N'' 
       ,@columnsDst nvarchar(max) = N'' 
       ,@sql        nvarchar(max)
       ,@KeyColumns nvarchar(max) = N'StudentID'
       ,@compatibility int = (
            select top 1 compatibility_level from sys.databases
            where name = db_name()
            order by Name
       );
declare @GroupBy nvarchar(max) = 
--case when @compatibility <= 90 
--   then case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns + ' with rollup' end
--   else case when len(@KeyColumns)=0 then '' else 'group by rollup (' + @KeyColumns + ')'    end
--end
case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns end

;

select  
    @columnsSrc += nchar(10) + N',' + quotename(SubjectID)
   ,@columnsDst += nchar(10) + N',sum(isnull(' + quotename(SubjectID) + N',0)) as ' 
                                               + quotename(SubjectID)
  from (
      select SubjectID
      from #ObtainedMarkEntry
      group by SubjectID
  ) as x
  order by x.SubjectID
;

set @sql = N'
select ' + 
  case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
  STUFF(@columnsDst, 1, 2, '') + '
from (
  select' + nchar(10) + 
    case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
'      SubjectID, ObtainedMarks
   from #ObtainedMarkEntry
) as j
pivot (
  sum(ObtainedMarks) for SubjectID in ('
  + stuff(replace(@columnsSrc, ',p.[', ',['), 2, 1, '')
  + ')
) as p' + nchar(10) +
@GroupBy +
';'
;

--print @sql;
exec sp_executesql @sql;
go

yields as desired:

StudentID   100         101         300
----------- ----------- ----------- -----------
1           90          70          0
2           65          0           55

T-sql – Dynamic SQL in a pivot table

You are saying:

EXEC @SQLString;

This is like saying:

EXEC N'SELECT 1;';

Which is probably something that looks like it will work on first glance, but go ahead and try it, it fails. You want:

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT ...;';
EXEC sys.sp_executesql @sql;

I wrote a pretty detailed article on dynamic pivot here:

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

But as for dynamic SQL, please see:

Best Answer

Related Solutions

Sql-server – Dynamic Number of Columns in Pivot Table

T-sql – Dynamic SQL in a pivot table

Related Question