SQL Server – Dynamic Parameters in Pivot

pivotsql serversql-server-2008-r2

I know the premise of a PIVOT() this syntax produces it perfectly for me:

Select *
FROM 
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot 
(
  SUM(Amt)
  ,for annual IN ([2014],[2015],[2016])
) p

However, what if I am using a stored procedure and passing a year1 and year2 to the pivot as variables? This will not work as incorrect syntax, but could this be achieved?

Create Procedure dbo.PivotParams
As
Declare @year1 varchar(100), @year2 varchar(100)

Set @year1 = '2014'
Set @year2 = '2016'

Select *
FROM 
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot 
(
  SUM(Amt) for annual BETWEEN @year1 and @year2
) p

Also the output should use COALESCE to prevent any NULL values in any of the pivot columns.

Best Answer

Sure, you can generate the list of years in dynamic SQL, but I suggest passing them as numbers (just one less thing that can go wrong if people pass in bad data, and I'm sure you didn't really mean to hard-code the values inside the procedure, defeats the purpose):

DECLARE @year1 smallint, @year2 smallint

SET @year1 = 2014;
SET @year2 = 2016;

DECLARE @years nvarchar(max) = N'', @select nvarchar(max) = N'';

SELECT  @years += N',' + QUOTENAME(y), @select += N',
  ' + QUOTENAME(y) + N' = COALESCE(' + QUOTENAME(y) + N',0)'
FROM 
(
  SELECT TOP (@year2-@year1+1) 
    y = @year1 - 1 + ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects ORDER BY [object_id]
) AS y;

DECLARE @sql nvarchar(max) = N'Select field1' + @select + N'
FROM 
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM dbo.Testing -- ALWAYS use schema prefix!
) x
pivot 
(
  SUM(Amt) for annual IN (' + STUFF(@years, 1, 1, N'') + N')
) p;';

PRINT @sql;

-- EXEC sys.sp_executesql @sql;

Slightly related:

Script to create dynamic PIVOT queries in SQL Server

Related Solutions

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

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 |

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

Best Answer

Related Solutions

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

Sql-server – Dynamic Number of Columns in Pivot Table

Related Question