T-SQL Rows To Columns (aka Pivot function)

When working with relational databases and statistics one often needs to present a table containing key and value in each row as a table with columns representing the key.

The following code is a quite generic solution to this problem. The code is a combination of the following two posts and my own stuff:

Pivot Query
Another Dynamic SQL CrossTab Stored Procedure

CREATE procedure [dbo].[RowsToColumns] (
-- Any select statement returning at least a pivot column and a value column
@Select VARCHAR(MAX),
-- The name of the column in @Select that will act as pivot
@PivotCol VARCHAR(100),
-- The name of the column in @Select that will act as value
@ValueCol VARCHAR(100),
-- The name of the column in @Select that will identify each row
@IdCol VARCHAR(100),
-- Other columns (comma separated) in @Select that we might want to return
@OtherCols VARCHAR(MAX) = NULL,
-- The columns (comma separated) in @Select that we should sort on
@OrderBy VARCHAR(MAX) = NULL )
AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

-- Initialize parameters
SET @OtherCols = isNull(', ' + @OtherCols,'')
SET @OrderBy = isNull(' ORDER BY ' + @OrderBy,'')

-- Temporary table holding all column names
CREATE TABLE #temp (Pivot VARCHAR(100))
INSERT INTO #temp
EXEC ('SELECT DISTINCT convert(VARCHAR(100),' + @PivotCol + ') AS Pivot FROM (' + @Select + ') A')

-- String containing all column names
DECLARE @Cols VARCHAR(8000)
SET @Cols = ''
SELECT @Cols = @Cols + ', MAX(CASE WHEN ' + @PivotCol + '=''' + Pivot + ''' THEN ' + @ValueCol + ' END) AS ['+Pivot+']'
FROM #temp
ORDER BY Pivot
DROP TABLE #temp

-- Create and execute dynamic sql
DECLARE @SQL VARCHAR(max)
SET @SQL = 'SELECT ' + @IdCol + @OtherCols + @Cols + ' FROM (' + @Select + ') A GROUP BY ' + @IdCol + @OtherCols + @OrderBy
EXEC (@SQL)

SET NOCOUNT OFF
SET ANSI_WARNINGS ON