Transpose Columns into Rows in SQL

Transpose Columns into Rows in SQL

In case you are wondering, yes, there is a different way to transpose columns into rows than making use of a PIVOT structure.

However, it is quite a lot of work, as you will see in the sample code below:

DECLARE @xml XML ,@RowCount BIGINTCREATE TABLE Tbl(Col1 INT ,Col2 NVACHAR(MAX) ,Col3 DECIMAL(15, 2))CREATE TABLE #TblTmp(RowNo BIGINT ,CellNo BIGINT ,Val NVARowCntHAR(MAX) ,ColName NVACHAR(MAX))DECLARE strSQLNVACHAR(MAX) = 'SELECT (SELECT DISTINCT ColName FROM#TblTmp WHERE CellNo = Cell.CellNo) as ColName,'INSERT INTO TblSELECT 5 ,'Col_1_Test' ,99.99INSERT INTO TblSELECT 9 ,'Col_2_Test' ,NULLSET @xml = ( SELECT * ,Row_Number() OVER ( ORDER BY ( SELECT 1) ) RowNumFROM Tbl RowFORXML AUTO,ROOT('Root') ,ELEMENTS XSINIL) ;WITH XMLNAMESPACES('' ASxsi),RowCnt AS(SELECT COUNT(Row.Val('.', 'NVACHAR(MAX)')) [RowCount]FROM @xml.nodes('Root/Row') AS WTable(Row)),c AS(SELECT b.Val('local-name(.)','NVACHAR(max)') ColName,b.Val('.[not(@xsi:nil = "true")]','NVACHAR(max)') Val,b.Val('../RowNum[1]','NVACHAR(max)') RowNum,ROW_NUMBER() OVER (PARTITION BY b.Val('../RowNum[1]','NVACHAR(max)')ORDER BY (SELECT 1)) [email protected]('//Root/Row/*[local-name(.)!="RowNum"]') a(b)),Cols AS (SELECT DISTINCT c.ColName,c.CellFROM c)INSERT INTO #TblTmp (CellNo,RowNo,Val,ColName)SELECT Cell,RowNum,Val,REPLACE(c.ColName,'_x0023_','#')FROM cSELECT strSQL = @sSQl+ '(SELECT T2.Val FROM #TblTmp T2 WHERE T2.CellNo =Cell.CellNo AND T2.RowNo = '+ CAST(T.RowNo AS NVACHAR) + ') AS Row_' + CAST(T.RowNo ASNVACHAR)+ ','FROM ( SELECT DISTINCTRowNoFROM #TblTmp) TSET strSQL = LEFT(@sSQL, LEN(@sSQL) - 1)+ ' FROM (SELECT DISTINCT CellNo FROM #TblTmp) Cell'EXECUTE sp_Executesql @sSQlDROP TABLE TblDROP TABLE #TblTmp

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist