Technical Article

usp_TransposeNRows v2.0

,

This is a stored procedure to transpose N rows from a specified table.

Version 2.0 allows the user to specify the schema in @TableName (the schema defaults to [dbo]).

I have found copying a transposed table to Excel useful for reviewing data from a table with many columns. A transposed table derived from a limited number of rows will not exceed Excel's maximum number of columns, and the transposed data is often easier to navigate and read.

Other transpose procedures I have found on the web are somewhat limited: they assume the columns in the original table are of the same numeric data type. This procedure handles tables with columns of varying data types; the workaround is to cast all data in the output table as varchar(max).

Also, please note that this procedure does NOT return a crosstab table. Crosstabs return summary data (totals or counts), not the original data. In certain specific circumstances (when the cases in a base table are unique) a crosstab procedure can return a transpose of the original data. Again, however, this use is limited to numeric data.

A portion of the procedure was adapted from MSDN community content. Thanks to the original posting member (Brindha Raji) and additional revising members. Please see the procedure code for the reference URL.

SYNTAX:

EXEC usp_TransposeNRows @TableName [, @NRows]

PARAMETERS:

@TableName Name of table to be transposed (REQUIRED)
Format: [<SchemaName>.]<TableName> (<SchemaName> defaults to [dbo])
@NRows Number of rows to transpose (OPTIONAL; Default = 0, max = 1022)
When @NRows = 0, just the column order and column names of the table are returned.
------------------------------------------------------------------------------
--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

Rate

5 (4)

Share

Share

Rate

5 (4)