Pivots Rule!! (Crosstab and Dynamic make meta data easy)

So today I'm pulling together something I found incredibly awesome and useful

Lot's of our sites are using database that for better or worse are starting to store data as metadata.
I'm not a particular fan because though generating the things that utilize this is easy, reporting against it is a whole other story. So I got into pivots.

So here's a simple example, of course this works great when you know the values in the table. But I'll show the starting point.

SELECT  [January]
  , [February]
  , [March]
FROM    ( SELECT    [Month]
              , SaleAmount
      FROM      Sales
    ) p PIVOT ( SUM(SaleAmount)
                FOR [Month]
                  IN ([January],[February],[March])
              ) AS pvt

But I wanted to do something that worked on something dynamic:
I started here with Jeff Smith's excellent Dynamic SQL Crosstab Stored procedure
(don't loose this as this will work great with older SQL servers)

So I created this Stored Procedure using the new Pivot function in SQL server.

CREATE procedure [dbo].[GetPivot]
( @Select varchar(8000),
  @PivotCol varchar(100),
  @Summaries varchar(100),
  @OtherCols varchar(8000) = Null)
AS
set nocount on
set ansi_warnings off
declare @Vals varchar(8000);
declare @sql varchar(8000);
declare @sqlView varchar(8000);
set @Vals = '';
set @OtherCols= isNull(@OtherCols + ',','')
set @sql = 'select distinct convert(varchar(100),' + @PivotCol + ') as TPivot FROM (' + @Select + ') A'
create table #temp  (TPivot varchar(100))
insert into #temp
exec (@sql)
select @Vals = @Vals + ', [' + TPivot +']'
from #Temp
drop table #Temp
select @Vals = RIGHT ( @vals , len(@vals)-1)
set @SQLView = 'SELECT ' + @otherCols + @vals + ' FROM (' + @Select + ') up  PIVOT (' + @Summaries + ' FOR ' + @PivotCol+ ' IN (' + @vals + ')) AS pvt'
--Print @SQLView
exec (@SQLView)
set nocount off

So in the case above you would do this

exec GetPivot
'SELECT    [Month]
              , SaleAmount
      FROM      Sales',
[Month],
'sum(SaleAmount)',
'Sales'

I wanted to throw in order by because our questions came in a particular sequence:
So here's one with order by:

CREATE procedure [dbo].[GetPivotWithOrder]
( @Select varchar(8000),
   @PivotCol varchar(100),
   @Summaries varchar(100),
   @OtherCols varchar(100) = Null,
 @OrderBy varchar(800) = Null)
AS
set nocount on
set ansi_warnings off
declare @Vals varchar(8000);
declare @sql varchar(8000);
declare @sqlView varchar(8000);
declare @OrderByText varchar(8000);
declare @SelectForPivot varchar(8000);
set @Vals = '';
set @OtherCols= isNull(@OtherCols + ',','')
set @OrderByText= isNull('Order By ' + @OrderBy,'')
set @SelectForPivot = replace(@Select, @OrderBy + ',','')
set @OrderBy= isNull(', ' + @OrderBy,'')
set @sql = 'select distinct convert(varchar(100),' + @PivotCol + ') as TPivot ' + @OrderBy + ' FROM (' + @Select + ' ) A ' + @OrderByText
Print @sql
create table #temp  (TPivot varchar(100), orderBy int)
insert into #temp
exec (@sql)
select @Vals = @Vals + ', [' + TPivot +']'
from #Temp
order by orderBy
drop table #Temp
select @Vals = RIGHT ( @vals , len(@vals)-1)
set @SQLView = 'SELECT ' + @otherCols + @vals + ' FROM (' + @SelectForPivot + ') up  PIVOT (' + @Summaries + ' FOR ' + @PivotCol+ ' IN (' + @vals + ')) AS pvt'
Print @SQLView
exec (@SQLView)
set nocount off

The commented out line --Print (@SQLView) will generate the actual pivot code from the dynamic information letting you create views or simple pivots

This has led to many quick solutions to difficult problems for a particular site and may lead to greater contributions in the future.