do_pivot.sql

-- =============================================
-- Author:        Cary Robbins
-- Create date:   3/9/2012
-- Description:   Returns a pivoted resultset from the table passed
-- =============================================
CREATE PROCEDURE [cmr].[sp_do_pivot]
    @table sysname,                     -- Name of the table with source data to pivot
    @column_field sysname,              -- Field used to generate new columns
    @value_field sysname = '',          -- Values to group within new columns
    @order_by sysname = NULL,           -- Optional order by field
    @function sysname = MAX,            -- Optional function to perform on values
    @reverse_columns bit = 0           -- If set to 1, reverses order of pivoted columns
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @pivot_fields nvarchar(max);

    declare @sql nvarchar(max) = N'
        -- Get unique values
        set @pivot_fields_OUT = (
            select distinct ''['' + CAST(' + @column_field + ' as nvarchar(max)) + ''],''
            from ' + @table + '
            order by 1 ' + case
                when @reverse_columns = 0 then 'asc' else 'desc'
            end + '
            for xml path('''')
        );
        -- Remove last comma
        set @pivot_fields_OUT = LEFT(@pivot_fields_OUT, LEN(@pivot_fields_OUT) - 1);
    ';

    exec sp_executesql @sql, N'@pivot_fields_OUT nvarchar(max) OUTPUT',
                       @pivot_fields_OUT=@pivot_fields OUTPUT;

    set @sql = N'
        select *
        from ' + @table + '
        pivot (
            ' + @function + '(' + @value_field + ')
            for ' + @column_field + ' in (' + @pivot_fields + ')
        ) as pvt ' + case
            when @order_by is null then ''
            else 'order by ' + @order_by
        end;

    exec(@sql);

END