×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Transpose Columns into Rows SQL
 

Submitted by: Hannes du Preez(ojdupreez1978@gmail.com)

Thursday Feb 15th 2018 by Hannes du Preez

Language: SQL, Expertise: Intermediate - Learn a different, albeit complicated, way to transpose columns into rows.

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
Home
Mobile Site | Full Site
Copyright 2018 © QuinStreet Inc. All Rights Reserved