Technical Article

usp_TransposeNRows

,

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

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)
@NRows - Number of rows to transpose (OPTIONAL; Default = 0, max = 1022)

(When @NRows = 0, the procedure returns a table containing just the column order and column names)

 

------------------------------------------------------------------------------
--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
------------------------------------------------------------------------------
--EXAMPLE:
--(Assuming a table, CalendarMonths, with columns {MonthID, MonthNN, MonthLabel, MonthName}, appropriately populated)
--EXEC usp_TransposeNRows 'CalendarMonths', 6
--
--RETURNS:
--ColOrderColNameRow001Row002Row003Row004Row005Row006
--1MonthID123456
--2MonthNN010203040506
--3MonthLabelJanFebMarAprMayJun
--4MonthNameJanuaryFebruaryMarchAprilMayJune
--
------------------------------------------------------------------------------
--ENTRY DATEAUTHOR NAMEVERSIONCOMMENTS
--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
--20090422David0375Revision
--20090331Mangal PardeshiRevision
--20090226Brindha RajiOriginal post
------------------------------------------------------------------------------
AS

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

SELECT@TableObjID = ID 
FROMSysobjects 
WHEREXType = 'u' 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.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) + ',' 
FROMSyscolumns 
WHEREID = @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) + '],' 
FROMSyscolumns 
WHEREID = @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
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

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating