3

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.

4

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:

| improve this answer | |
-1

Here is a dynamic SQL pivot procedure I wrote. It allows you to pass in any table or any query (SELECT statement) containing values and columns that you want to pivot. It does use temporary tables, so the tempdb might grow a little.

/*
******************************************************************
**  Copyright c 2016 Kurt Keydel
******************************************************************
Object Name :       uspBuildPivotReport
Object Type :       Stored Procedure
Created By  :       KKeydel 
Usage       :           REPORT

Description :       
This procedure takes a table or query with expandable
columns and creates a crosstab or matrix report
with aggregate values (such as SUM, COUNT, MIN, MAX, etc.)

The utility of this report is that the actual number of columns
for aggregate values does not need to be known ahead of time. 
This procedure will take all passed in pivot values and 
construct the columns automatically.


Parameters  :
@Table_or`_Query:   
        The name of the table or query containing the
    raw data to be pivoted. If passing in a query,
    it must be contained within single quotes. Depending
    on the version of SQL Server, a table name may not
    need to be bracketed by single quotes.

@CrosstabColumnSource:  
        The name of the column containing the crosstab or
    dimensional names, e.g. months of the year, regions,
    or product types.

@AggregateOperator: 
        The aggregate function to be used to calculate
    values under the crosstab columns, e.g. SUM,
    AVG, MIN, MAX, COUNT, etc.

@DataColumnName     
        The column containing the values to be calculated.

@SaveToTableName(Optional) 
    The name of an output table in which to         
    insert the crosstab results.
*/

    create procedure dbo.uspBuildPivotReport (

     @Table_or_Query varchar(5000)
    ,@CrosstabColumnSource varchar(255)
    ,@AggregateOperator varchar(50)
    ,@DataColumnName varchar(100)
    ,@SaveToTableName varchar(255)=NULL
        )
    AS
    SET NOCOUNT ON
    BEGIN

    declare @sql varchar(max)
    declare @RowColumnList varchar(8000)
    declare @crosstabColumnAssignment varchar(max)
    declare @crosstabColumnArray varchar(8000)
    declare @FullColumnList varchar(8000)
    declare @switch int

    IF object_id('tempdb..##RawData') is not null drop table ##RawData

    if charindex('select',@Table_or_Query) > 0
        BEGIN
            select @sql = 'SELECT * INTO dbo.##RawData FROM (' + @Table_or_Query + ') t1'
            select @switch = 1
        END
        ELSE
        BEGIN
            select @sql = 'SELECT * INTO dbo.##RawData FROM ' + @Table_or_Query + ' t1'
            select @switch = 0
        END

    exec (@sql)

    select @RowColumnList = ''
    select @RowColumnList = @RowColumnList + COLUMN_NAME + ', '
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = '##RawData'
    AND COLUMN_NAME != @CrosstabColumnSource
    AND COLUMN_NAME != @DataColumnName

    select @RowColumnList = REVERSE(SUBSTRING(REVERSE(@RowColumnList), CHARINDEX(',', REVERSE(@RowColumnList)) + 1,LEN(@RowColumnList)))

    select @FullColumnList = ''
    select @FullColumnList = @FullColumnList + COLUMN_NAME + ', '
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = '##RawData'

    select @FullColumnList = REVERSE(SUBSTRING(REVERSE(@FullColumnList), CHARINDEX(',', REVERSE(@FullColumnList)) + 1,LEN(@FullColumnList)))

    select @crosstabColumnAssignment = ''
    select @sql = 'SELECT DISTINCT ' + @CrosstabColumnSource + '  from ##RawData order by ' + @CrosstabColumnSource

    create table #Columns (
     RowID int identity(1,1)
    ,Value varchar(100))

    insert #Columns(Value)
    exec(@sql)

    select @CrosstabColumnArray = ''
    select @CrosstabColumnArray = @CrosstabColumnArray + '[' + value + '], ' from #Columns
    select @CrosstabColumnArray = REVERSE(SUBSTRING(REVERSE(@CrosstabColumnArray), CHARINDEX(',', REVERSE(@CrosstabColumnArray)) + 1,LEN(@CrosstabColumnArray)))

    select @crosstabColumnAssignment = ''
    select @crosstabColumnAssignment = @crosstabColumnAssignment + ' ISNULL([' + Value + '],''0'') as ''' + value + ''', ' from #Columns
    select @crosstabColumnAssignment = REVERSE(SUBSTRING(REVERSE(@crosstabColumnAssignment), CHARINDEX(',', REVERSE(@crosstabColumnAssignment)) + 1,LEN(@crosstabColumnAssignment)))

    select @sql = 'SELECT ' + @RowColumnList + ', ' + @crosstabColumnAssignment + char(10) + char(13)

    if @switch = 1
    select @sql = @sql + 'FROM ' + char(10) + char(13) + '( SELECT ' + @FullColumnList + ' FROM (' + @Table_or_Query + ') t1) t2' + char(10) + char(13)
    else
    select @sql = @sql + 'FROM ' + char(10) + char(13) + '( SELECT ' + @FullColumnList + ' FROM ' + @Table_or_Query + ') t1' + char(10) + char(13)

    select @sql = @sql + 'PIVOT '  + char(10) + char(13)
    select @sql = @sql + '(' + char(10) + char(13)
    select @sql = @sql + @AggregateOperator + '(' + @DataColumnName + ') ' + char(10) + char(13)
    select @sql = @sql + 'FOR ' + @CrosstabColumnSource + ' IN ( ' + @crosstabColumnArray + ')) as PVT'

    declare @sql2 varchar(max)

    select @sql2 = 'SELECT * INTO ##Crosstab FROM (' + @sql + ') c' 
    exec(@sql2)

            IF ISNULL(@SaveToTableName, '') != ''
            BEGIN

                if exists(select * from Sysobjects where xtype = 'U' and name = @SaveToTableName) select @SQL = 'drop table ' + @SaveToTableName
                exec (@SQL)
                select @SQL = 'SELECT DISTINCT * INTO ' + @SaveToTableName + ' FROM ##Crosstab'
                exec (@SQL)

            END
    select * from ##Crosstab

    drop table ##Crosstab
    drop table ##RawData
    END
| improve this answer | |
  • Could you give an example of how you would execute this stored procedure please? – Ruahl Behr Aug 28 '18 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.