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('http://www.w3.org/2001/XMLSchema-instance' 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