usp_TransposeNRows v2.0

--	----------------------------------------------------------------------------
--	usp_TransposeNRows
--	Transpose the first N rows of a table
--	----------------------------------------------------------------------------
IF OBJECT_ID(N'usp_TransposeNRows', N'P') IS NOT NULL DROP PROCEDURE usp_TransposeNRows
GO

CREATE PROCEDURE [dbo].[usp_TransposeNRows]
	(
	@TableName	VARCHAR(128), 
	@NRows		INT = 0
	)
--	----------------------------------------------------------------------------
--	usp_TransposeNRows
--	Transpose the first N rows of a table
--	----------------------------------------------------------------------------
--	SYNTAX:		EXEC usp_TransposeNRows @TableName [, @NRows]
--	PARAMETERS:	@TableName	Name of table to be transposed	(REQUIRED)
--				@NRows		Number of rows to transpose		(OPTIONAL; Default = 0)
--							When @NRows = 0, the procedure returns just the column order and column names
--				@Debug		Don't execute / execute debug statements (OPTIONAL; Default = 0)
--	----------------------------------------------------------------------------
--	EXAMPLE:		
--	(Assuming a table, testing.Calendar_Months, with columns {MonthID, MonthNN, MonthLabel, MonthName}, appropriately populated)
--	EXEC usp_TransposeNRows 'testing.Calendar_Months', 6
--
--	RETURNS:
--	ColOrder	ColName		Row001	Row002		Row003	Row004	Row005	Row006
--	1			MonthID		1		2			3		4		5		6
--	2			MonthNN		01		02			03		04		05		06
--	3			MonthLabel	Jan		Feb			Mar		Apr		May		Jun
--	4			MonthName	January	February	March	April	May		June
--
--	----------------------------------------------------------------------------
--	ENTRY DATE	AUTHOR NAME	VERSION	COMMENTS
--	20100129	Jeff Brooks	v2.0	Revised code to allow @TableName to include a schema name ('<schemaname>.<tablename>')
--	20100118	Jeff Brooks	v1.0	Expanded on code from MSDN community content to create a truly transposed table
--									NOTE: All values from the original table are cast as varchar(max) to avoid data typing issues in the transposed table.
--
--	Code used to populate #SemiTransTable table (used here with modifications) is from http://msdn.microsoft.com/en-us/library/ms177410.aspx
--	20090422	David0375
--	20090331	Mangal Pardeshi
--	20090226	Brindha Raji
--	----------------------------------------------------------------------------
AS

BEGIN
DECLARE	@SchemaName		VARCHAR(128)
DECLARE	@SchemaID		INT
DECLARE @TableObjID		INT
DECLARE	@ListCol		VARCHAR(MAX)
DECLARE	@ListUnPivotCol	VARCHAR(MAX)
DECLARE	@DynSQL			VARCHAR(MAX)
DECLARE @RowCount		INT
DECLARE @RowColName		VARCHAR(MAX)
DECLARE @NCols			INT
DECLARE @StartCol		INT
DECLARE @ColGroup		INT
DECLARE @MaxRows		INT

SET		@SchemaName		= 'dbo'
SET		@SchemaID		= 1
SET		@TableObjID		= 0
SET		@ListCol		= ''
SET		@ListUnPivotCol	= ''
SET		@DynSQL			= ''
SET		@StartCol		= 1
SET		@ColGroup		= 10	--	Adjust this parameter if you want; Higher runs faster, but may cause generated SQL code to overflow @DynSQL

--	----------------------------------------------------------------------------
--	Parse the table name to check for a schema name
IF	PATINDEX('%.%',@TableName) > 0
	BEGIN
	SET @SchemaName = SUBSTRING(@TableName, 1, PATINDEX('%.%',@TableName)-1)
	SET @TableName = SUBSTRING(@TableName, PATINDEX('%.%',@TableName)+1, 128)
	SET @SchemaID = (SELECT Schema_ID FROM sys.schemas WHERE name = @SchemaName)
	END

--	----------------------------------------------------------------------------
--	Ensure the return table won't have too many columns
SET	@MaxRows = 1024	--	MS SQL defined maximum number of columns in a non-wide table
IF	@NRows > (@MaxRows - 2) SET @NRows = (@MaxRows - 2)	--	(Two columns are always included in the output table: ColOrder and ColName)

--	----------------------------------------------------------------------------
--	Set up semi-transposed table
--	This table has @NRows sets of @NCols rows
IF OBJECT_ID(N'tempdb..#SemiTransTable', N'U') IS NOT NULL 
	DROP TABLE #SemiTransTable
	
CREATE TABLE	#SemiTransTable
	(
	ColName		VARCHAR(128),
	ColVal		VARCHAR(MAX),
	RowCol		INT IDENTITY(1,1),
	RowNum		INT
	)

SELECT	@TableObjID = ID 
FROM	Sysobjects 
WHERE	XType = 'u'
	AND uid = @SchemaID
	AND	Name = @TableName

SELECT	@NCols = MAX(colid) 
FROM	Syscolumns 
WHERE	ID = @TableObjID

--	----------------------------------------------------------------------------
--	Set up transposed table
IF OBJECT_ID(N'tempdb..#TransTable', N'U') IS NOT NULL 
	DROP TABLE #TransTable

CREATE TABLE	#TransTable
	(
	ColOrder	INTEGER,
	ColName		VARCHAR(128)
	)

INSERT INTO	#TransTable
SELECT	sc.colorder as ColOrder, 
		sc.name as ColName
FROM	syscolumns as sc
INNER JOIN	
		sysobjects as so 
	ON	sc.id = so.id
WHERE	so.uid = @SchemaID
	AND	so.name = @TableName

--	----------------------------------------------------------------------------
--	Add RowNNN columns to #TransTable
SET	@RowCount = 1
WHILE	@RowCount <= @NRows
	BEGIN
	SET @RowColName = 'Row' + REPLICATE('0', 3-LEN(CAST(@RowCount AS VARCHAR(4)))) + CAST(@RowCount AS VARCHAR(4))
	SET @DynSQL = 'ALTER TABLE #TransTable ADD ' + @RowColName + ' VARCHAR(MAX)'
	EXEC(@DynSQL)
		SET @RowCount = @RowCount + 1
	END

--	----------------------------------------------------------------------------
--	Populate #TransTable 
--	Note:	Due to VARCHAR(MAX) truncation issues with dynamic SQL, populating the table is done for @ColGroup columns at a time
--			This workaround is required because the TEXT data type is not valid for local variables, so MAX = 8,000
WHILE	@StartCol <= @NCols
	BEGIN
	SELECT @ListCol = 
		(
		SELECT	'CAST(' + CAST(name AS VARCHAR(128)) + ' AS VARCHAR(MAX)) AS ' + CAST(name AS VARCHAR(128)) + ',' 
		FROM	Syscolumns 
		WHERE	ID = @TableObjID AND colid BETWEEN @StartCol AND @StartCol + @ColGroup - 1
		FOR XML PATH('')
		)
	SET @ListCol = SUBSTRING(@ListCol, 1, LEN(@ListCol)-1)

	SELECT @ListUnPivotCol = 
		(
		SELECT	'[' + CAST(name AS VARCHAR(128)) + '],' 
		FROM	Syscolumns 
		WHERE	ID = @TableObjID AND colid BETWEEN @StartCol AND @StartCol + @ColGroup - 1
		FOR XML PATH('')
		)
	SET @ListUnPivotCol = SUBSTRING(@ListUnPivotCol, 1, LEN(@ListUnPivotCol)-1)

	SET @DynSQL = 
			'INSERT INTO #SemiTransTable (ColName, ColVal) ' 
		+	'SELECT tblPivot.Pr, tblPivot.Val '
		+	'FROM (SELECT TOP ' + CAST(@NRows AS VARCHAR(8)) + ' ' + @ListCol + ' FROM ' + @SchemaName + '.' + @TableName + ') Table1 '
		+	'UNPIVOT (Val For Pr In (' + @ListUnPivotCol + ')) as tblPivot'
	
	TRUNCATE TABLE #SemiTransTable
	EXEC (@DynSQL)
	
	--	----------------------------------------------------------------------------
	--	Update #SemiTransTable table with calculated row numbers
	UPDATE	#SemiTransTable
	SET		RowNum = FLOOR((RowCol-1)/CAST(CASE WHEN (@NCols - @StartCol) < @ColGroup THEN (@NCols - @StartCol + 1) ELSE @ColGroup END AS FLOAT)) + 1

	--	----------------------------------------------------------------------------
	--	Add transposed values to #TransTable
	SET @RowCount = 1
	WHILE @RowCount <= @NRows
		BEGIN
		SET @RowColName = 'Row' + REPLICATE('0', 3-LEN(CAST(@RowCount AS VARCHAR(4)))) + CAST(@RowCount AS VARCHAR(4))

		SET @DynSQL = 
			'UPDATE #TransTable SET ' 
			+ @RowColName 
			+ ' = tv.ColVal FROM #TransTable INNER JOIN (SELECT * FROM #SemiTransTable WHERE RowNum = ' 
			+ CAST(@RowCount AS VARCHAR(4)) + ') AS tv ON #TransTable.ColName = tv.ColName'
		EXEC(@DynSQL)
		
		SET @RowCount = @RowCount + 1
		END

	SET @StartCol = @StartCol + @ColGroup
	END

--	----------------------------------------------------------------------------
--	Return the transposed table
SELECT	*
FROM	#TransTable

--	----------------------------------------------------------------------------
--	Clean up
DROP TABLE	#SemiTransTable
DROP TABLE	#TransTable

END
GO