usp_TransposeNRows

--	----------------------------------------------------------------------------
--	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
--	----------------------------------------------------------------------------
--	EXAMPLE:		
--	(Assuming a table, CalendarMonths, with columns {MonthID, MonthNN, MonthLabel, MonthName}, appropriately populated)
--	EXEC usp_TransposeNRows 'CalendarMonths', 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
--	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		Revision
--	20090331	Mangal Pardeshi		Revision
--	20090226	Brindha Raji		Original post
--	----------------------------------------------------------------------------
AS

BEGIN
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	@TableObjID	= 0
SET	@ListCol	= ''
SET	@ListUnPivotCol	= ''
SET	@DynSQL		= ''
SET	@StartCol	= 1
SET	@ColGroup	= 50	-- Adjust this parameter if you want


--	----------------------------------------------------------------------------
--	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	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.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(' + CONVERT(VARCHAR,Name) + ' AS VARCHAR(MAX)) AS ' + CONVERT(VARCHAR,Name) + ',' 
		FROM	Syscolumns 
		WHERE	ID = @TableObjID AND colid >= @StartCol AND colid < @StartCol + @ColGroup
		FOR XML PATH('')
		)
	SET @ListCol = SUBSTRING(@ListCol, 1, LEN(@ListCol)-1)

	SELECT @ListUnPivotCol = 
		(
		SELECT	'[' + CONVERT(VARCHAR,Name) + '],' 
		FROM	Syscolumns 
		WHERE	ID = @TableObjID AND colid >= @StartCol AND colid < @StartCol + @ColGroup
		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 ' + @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