In my quest to find the perfect PIVOT, I have run across a few different methodologies.  Some of those found right here.  All of them in some way shape or form required doing a dynamic SQL exec.  Moreover, the dynamic nature of a PIVOT would require you to have a custom query for each different PIVOT.  My methodology doesn't differ completely from all the methods I have seen, but tries to take best of all approaches and put it in one succinct implementation.  My solution utilizes a .NET CLR stored procedure.

The procedure will ultimately take in a few parameters.

@query nvarchar(4000)  - This is where you do your select and always selecting into #temp, which will be in scope.  I don't like using temp tables but it is the easiest way to accomplish this task.

@pivotColumn nvarchar(4000) - In retrospect I should have just limited this to the max size of a column name, but this is just an educational post and you can change this to your liking.  As it states...this is the column which you want to pivot your data on.

@selectCols nvarchar(4000) - A comma delimited list of columns that you will want to select from.  This must include the aggregate field as well as the @pivotColumn.  I would also suggest another field for the row analytics ie PayMethod, OrderHour, ProductTotal where PayMethod or OrderHour could be my pivotColumn and ProductTotal will be my aggregate.

@aggCols nvarchar(4000) - Your aggregate ie 'sum(ProductTotal)'

@orderBy nvarchar(4000) - You guessed it...how you want your result ordered, typically by one of your select columns and not the pivot or aggregate column.

Here is a sample call:

DECLARE @query nvarchar(4000)
DECLARE @pivotColumn nvarchar(4000)
DECLARE @selectCols nvarchar(4000)
DECLARE @aggCols nvarchar(4000)
DECLARE @orderBy nvarchar(4000)
set @query = 'select PayMethod, datepart(hh, OrderDate) as OrderHour, ProductTotal into #temp from dbo.Orders (nolock) where OrderDate between getdate()-1 and getdate() and division = ''15'''
set @pivotColumn = 'PayMethod'
set @selectCols = 'PayMethod, OrderHour, ProductTotal'
set @aggCols = 'sum(ProductTotal)'
set @orderBy = ''
EXECUTE dbo.clrDynamicPivot
   @query
  ,@pivotColumn
  ,@selectCols
  ,@aggCols
  ,@orderBy
GO

So now lets talk about the CLR procedure.  If you have never built a SQL CLR procedure.  Perhaps you can grab one of your .NET guys and he can help you out.  I wrote a blog on how to create a CLR function http://throwex.blogspot.com/2010/11/sql-clr-function.html .

Once you feel confident enough to build a CLR function or procedure you can continue.

I will try to explain what is going on in the DynamicPivot.cs file that was attached to the post.

clrDynamicPivot is the public method that will ultimately be exposed to you as a stored procedure.  As you can see it has the inputs that I described above.  The name of the procedure will be "clrDynamicPivot" if you do not change anything.  

What this method will do is take the query that you wrote and execute it creating a temporary table #temp with all the fields that you specified in your query.  Once that query has executed successfully it will then call a method that will collect all the unique data as columns that you selected in your pivotColumn.  As it builds the final select from all the data that you passed in, it will utilize the same database connection that your underlying stored procedure is using, so there is no need to pass in credentials.

[Microsoft.SqlServer.Server.SqlProcedure(Name="clrDynamicPivot")]
public static void clrDynamicPivot(SqlString query, SqlString pivotColumn, SqlString selectCols, SqlString aggCols, SqlString orderBy)
   {
    string stmt = string.Empty;
    try
     {
       CreateTempTable(query);
       string pivot = GetPivotData(pivotColumn.ToString());
       stmt = string.Format("select * from ( select {0} from #temp ) as t pivot ( {1} for {2} in ( {3} )) as p {4}",
       selectCols.ToString(),
       aggCols.ToString(),
       pivotColumn.ToString(),
       pivot,
       orderBy.ToString());
       using (SqlConnection cn = new SqlConnection("Context Connection=True"))
         {
          SqlCommand cmd = cn.CreateCommand();
          cmd.CommandText = stmt;
          cn.Open();
          SqlDataReader reader = cmd.ExecuteReader();
          SqlContext.Pipe.Send(reader);
         }
 
      }
     catch (Exception ex)
      {
       throw new Exception(string.Format("clrDynamicPivot Error stmt:{0}", stmt), ex);
      }
 }

This method is used for generating the #temp table utilizing the same database connection that your procedure is running under.  It's a void so it doesn't return anything.  If this executes properly you will have the #temp table populated with the results of your query.

        public static void CreateTempTable(SqlString query)
        {
            using (SqlConnection sqlconn = new SqlConnection("Context Connection=True"))
            {
                SqlCommand sqlCmd = sqlconn.CreateCommand();
                sqlCmd.CommandText = query.ToString();
                sqlconn.Open();
                sqlCmd.ExecuteNonQuery();
            }
        }

This method's job is to sqlect all of the unique rows from your #temp query based upon your pivot column.  It will then loop through a SqlDataReader and generate comma delimited string of unique columns that could only be determined at run time.  When you design your query, there would be no way for you to know the set of unique rows that would ultimately be selected.  If you did know that information, there would be no need for you to do any dynamic pivots.  Keep in mind that I am only looking at 3 different types of fields.  This should either be expanded for other types of data or if someone can find a better way then by all means do it.  Once again this is just an educational post.

        public static string GetPivotData(string pivotColumn)
        {
            string stmt = string.Format("select distinct {0} from #temp", pivotColumn);
            string pivotCols = string.Empty;
 
            using (SqlConnection cn = new SqlConnection("Context Connection=True"))
            {
                SqlCommand cmd = cn.CreateCommand();
                cmd.CommandText = stmt;
                cn.Open();
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        if (dr.GetFieldType(0) == typeof(System.Int32))
                            pivotCols += "[" + dr.GetInt32(0) + "],";
                        if (dr.GetFieldType(0) == typeof(System.Decimal))
                            pivotCols += "[" + dr.GetDecimal(0) + "],";
                        if (dr.GetFieldType(0) == typeof(System.String))
                            pivotCols += "[" + dr.GetString(0) + "],";
                    }
                }
            }
            return pivotCols.Remove(pivotCols.Length - 1);
        }

The results that are returned are shown below.