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]
(
@TableNameVARCHAR(128), 
@NRowsINT = 0
)
------------------------------------------------------------------------------
--usp_TransposeNRows
--Transpose the first N rows of a table
------------------------------------------------------------------------------
--SYNTAX:EXEC usp_TransposeNRows @TableName [, @NRows]
--PARAMETERS:@TableNameName of table to be transposed(REQUIRED)
--@NRowsNumber of rows to transpose(OPTIONAL; Default = 0)
--When @NRows = 0, the procedure returns just the column order and column names
--@DebugDon'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:
--ColOrderColNameRow001Row002Row003Row004Row005Row006
--1MonthID123456
--2MonthNN010203040506
--3MonthLabelJanFebMarAprMayJun
--4MonthNameJanuaryFebruaryMarchAprilMayJune
--
------------------------------------------------------------------------------
--ENTRY DATEAUTHOR NAMEVERSIONCOMMENTS
--20100129Jeff Brooksv2.0Revised code to allow @TableName to include a schema name ('<schemaname>.<tablename>')
--20100118Jeff Brooksv1.0Expanded 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
--20090422David0375
--20090331Mangal Pardeshi
--20090226Brindha Raji
------------------------------------------------------------------------------
AS

BEGIN
DECLARE@SchemaNameVARCHAR(128)
DECLARE@SchemaIDINT
DECLARE @TableObjIDINT
DECLARE@ListColVARCHAR(MAX)
DECLARE@ListUnPivotColVARCHAR(MAX)
DECLARE@DynSQLVARCHAR(MAX)
DECLARE @RowCountINT
DECLARE @RowColNameVARCHAR(MAX)
DECLARE @NColsINT
DECLARE @StartColINT
DECLARE @ColGroupINT
DECLARE @MaxRowsINT

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
IFPATINDEX('%.%',@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
(
ColNameVARCHAR(128),
ColValVARCHAR(MAX),
RowColINT IDENTITY(1,1),
RowNumINT
)

SELECT@TableObjID = ID 
FROMSysobjects 
WHEREXType = 'u'
AND uid = @SchemaID
ANDName = @TableName

SELECT@NCols = MAX(colid) 
FROMSyscolumns 
WHEREID = @TableObjID

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

CREATE TABLE#TransTable
(
ColOrderINTEGER,
ColNameVARCHAR(128)
)

INSERT INTO#TransTable
SELECTsc.colorder as ColOrder, 
sc.name as ColName
FROMsyscolumns as sc
INNER JOIN
sysobjects as so 
ONsc.id = so.id
WHEREso.uid = @SchemaID
ANDso.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)) + ',' 
FROMSyscolumns 
WHEREID = @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)) + '],' 
FROMSyscolumns 
WHEREID = @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
SETRowNum = 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