T-SQL: Display Horizontal Rows Vertically

  • Article

This article is an outcome of my answer to this question on MSDN forum.

Consider this scenario:

Table 1:

DEPARTMENT

EMPID

ENAME

SALARY

A/C

1

TEST1

2000

SALES

2

TEST2

3000

Table 2:

ColumnName

1

2

DEPARTMENT

A/C

SALES

EMPID

1

2

ENAME

TEST1

TEST2

SALARY

2000

3000

If we are required to transform result set in Table1 format to Table2 format:

**How to display dynamically horizontal rows vertically:
**
To display dynamically horizontal rows vertically, we used the technique of dynamic unpivoting (using XQuery and nodes() method) and then dynamic pivoting

Below code block will transform result set in Table1 format to Table2 format.

DECLARE @EMPLOYEE TABLE (DEPARTMENT VARCHAR(20),EMPID INT,ENAME VARCHAR(20),SALARY INT) 

INSERT @EMPLOYEE SELECT 'A/C',01,'TEST1',2000 

INSERT @EMPLOYEE SELECT 'SALES',02,'TEST2',3000 

  

SELECT * FROM @EMPLOYEE 

  

DECLARE @Xmldata XML = (SELECT * FROM @EMPLOYEE FOR XML PATH('') )   

  

--Dynamic unpivoting 

SELECT * INTO ##temp FROM  ( 

SELECT 

ROW_NUMBER()OVER(PARTITION BY  ColumnName ORDER  BY ColumnValue) rn,* FROM ( 

SELECT i.value('local-name(.)','varchar(100)') ColumnName, 

       i.value('.','varchar(100)') ColumnValue 

FROM @xmldata.nodes('//*[text()]') x(i) ) tmp ) tmp1 

--SELECT * FROM ##temp 

  

--Dynamic pivoting 

DECLARE @Columns NVARCHAR(MAX),@query NVARCHAR(MAX) 

SELECT @Columns = STUFF( 

 (SELECT  ', ' +QUOTENAME(CONVERT(VARCHAR,rn)) FROM

 (SELECT DISTINCT  rn FROM  ##temp  ) AS  T FOR  XML PATH('')),1,2,'')  

SET @query = N' 

SELECT ColumnName,' + @Columns + '  

FROM 

( 

  SELECT * FROM ##temp 

)  i 

PIVOT 

( 

  MAX(ColumnValue) FOR rn IN ('

  + @Columns 

  + ') 

)  j ;'; 

  

EXEC (@query) 

--PRINT @query 

DROP TABLE  ##temp

See Also