dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Tip of the Day
Language: SQL
Expertise: Intermediate
Feb 15, 2018

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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 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
Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
×
By using this site, you agree to the Privacy Policy
Thanks for your registration, follow us on our social networks to keep up-to-date