An easy technique for inverting a SQL table

The following is an easy technique for inverting a table structure in T-SQL. I will explain the mechanics behind the technique and provide the sample code that we used on a recent project. The original code was more than 200 lines and was extremely complex. While the code was more than sufficient for our environment, there may be other ways that accomplish the same goal, this post aims to present one of the available options.

The first step is to create a temp table that holds the table structure containing the rows of data that are going to be inverted into columns. You need to add an additional integer column that contains the row number for each of the rows in the table. This is automatically populated using the Row_Number() function.

Declare @Count int, @i int, @SQL nVarchar(3000) Create Table #tmp(ModuleHierarchyID Int, WebPartCode Varchar(10), SVGFilename Varchar(255), XSLTFilename Varchar(255), RowNumber Int)

The next step is to populate the temp table with the data that you want to invert into a column structure.

Insert into #tmp(ModuleHierarchyID, WebPartCode, SVGFilename, XSLTFilename, RowNumber) Select ModuleHierarchyID, W.WebPartCode, SVGFilename, XSLTFilename,Row_Number() Over(ORDER BY ModuleHierarchyId DESC)As RowNumber from SVG_SVGMapping S, WEP_WebPart W where S.ModuleHierarchyId= @ModuleHierarchyID and S.WebPartId= W.WebPartId

The data from the query above looks as follows






The following section dynamically builds a sql query that extracts the tabular data and presents it in a column format. The @Count variabledetermines the depth of the table and uses this to iterate through each row using the rownumber to build each column. The dynamic SQL below contains a global select statement, with sub select statements for each column.

Set@Count = (Selectcount(1) from #tmp) Set @i = 0 Set @SQL = N'Select ' + Ltrim(str(@ModuleHierarchyID)) + ' As ModuleHierarchyId' While@i < @Count BeginSet @SQL = @SQL+ N', ' Set @SQL = @SQL + N'(Select WebPartCode from #tmp where Rownumber = ' +ltrim(Str(@i + 1)) + ') As WebPartCode' + ltrim(str(@i + 1))+ ',' Set @SQL = @SQL +N'(Select SVGFilename from #tmp where Rownumber = '+ ltrim(Str(@i+ 1)) + ') As SVGFilename' + ltrim(str(@i + 1)) + ',' Set @SQL = @SQL +N'(Select XSLTFilename from #tmp where Rownumber = '+ ltrim(Str(@i + 1)) + ') As XSLTFilename' + ltrim(str(@i+ 1)) Set @i = @i +1 End EXECUTE sp_executesql @SQL

The dynamic SQL should look as follows:

Select 4 As ModuleHierarchyId,Select WebPartCode from #tmp where Rownumber = 1) As WebPartCode1,
Select SVGFilename from #tmp where Rownumber = 1) As SVGFilename1,
Select XSLTFilename from #tmp where Rownumber = 1) As XSLTFilename1,
Select WebPartCode from #tmp where Rownumber = 2) As WebPartCode2,
Select SVGFilename from #tmp where Rownumber = 2) As SVGFilename2,
Select XSLTFilename from #tmp where Rownumber = 2) As XSLTFilename2)




Once the query is run the following results are returned:



The complete code looks as follows:

Declare@Count int, @i int, @SQL nVarchar(3000) CreateTable #tmp(ModuleHierarchyID Int, WebPartCode Varchar (10), SVGFilename Varchar(255), XSLTFilename Varchar(255), RowNumber Int) Insert into #tmp(ModuleHierarchyID,WebPartCode, SVGFilename, XSLTFilename, RowNumber) Select ModuleHierarchyID, W.WebPartCode , SVGFilename, XSLTFilename, Row_Number()Over (ORDERBY ModuleHierarchyId DESC) As RowNumber from SVG_SVGMapping S, WEP_WebPart W where S.ModuleHierarchyId = @ModuleHierarchyID and S.WebPartId= W.WebPartId Set @Count = (Select count(1) from #tmp) Set @i = 0 Set @SQL = N'Select ' +Ltrim(str(@ModuleHierarchyID)) + ' As ModuleHierarchyId' While @i < @Count Begin Set @SQL = @SQL + N', ' Set + N'(Select WebPartCode from #tmp where Rownumber = ' + ltrim(Str(@i + 1))+ ') As WebPartCode' + ltrim(str(@i + 1)) + ',' Set @SQL = @SQL + N'(Select SVGFilename from #tmp where Rownumber = ' +ltrim(Str(@i + 1)) +') As SVGFilename' +ltrim(str(@i + 1)) +',' Set @SQL = @SQL + N'(Select XSLTFilename from #tmp where Rownumber = ' + ltrim(Str(@i + 1))+ ') As XSLTFilename' + ltrim(str(@i + 1)) Set @i = @i + 1 End EXECUTE sp_executesql @SQL