dcsimg
Login | Register   
LinkedIn
Google+
Twitter
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.


advertisement
 

Tip: Transpose Columns into Rows in SQL

Learn a different way to transpose columns into rows.


advertisement

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


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

 





   
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date