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 BIGINT
CREATE 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 Tbl
SELECT 5 ,
'Col_1_Test' ,
99.99
INSERT INTO Tbl
SELECT 9 ,
'Col_2_Test' ,
NULL
SET @xml = ( SELECT * ,
Row_Number() OVER ( ORDER BY ( SELECT 1
) ) RowNum
FROM Tbl Row
FOR
XML AUTO,
ROOT('Root') ,
ELEMENTS XSINIL
) ;
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' AS
xsi),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)) Cell
FROM
@xml.nodes('//Root/Row/*[local-name(.)!="RowNum"]') a(b)
),Cols AS (
SELECT DISTINCT c.ColName,
c.Cell
FROM c
)
INSERT INTO #TblTmp (CellNo,RowNo,Val,ColName)
SELECT Cell,RowNum,Val,REPLACE(c.ColName,'_x0023_','#')
FROM c
SELECT 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 AS
NVACHAR)
+ ','
FROM ( SELECT DISTINCT
RowNo
FROM #TblTmp
) T
SET strSQL = LEFT(@sSQL, LEN(@sSQL) - 1)
+ ' FROM (SELECT DISTINCT CellNo FROM #TblTmp) Cell'
EXECUTE sp_Executesql @sSQl
DROP TABLE Tbl
DROP TABLE #TblTmp
Visit the DevX Tip Bank
