Resources For IT Professionals
Page Details
  • First published by (8Microsoft Community Contributo)
  • When: 13 Sep 2013 10:52 PM
  • Last revision by (eMicrosoft Partne)
  • When: 17 Sep 2013 9:20 PM
  • Revisions: 7
  • Comments: 6
Options

Revision #3

Wiki  >  TechNet Articles  >  T-SQL: Display Horizontal Rows Vertically  >  Revision #3
T-SQL: Display Horizontal Rows Vertically
You are currently reviewing an older revision of this page.
Go to current version
This article is outcome of my answer to this question on MSDN forumn .

Consider below 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 resultset in Table1 format to Table2 format  :

How to display dynamically horizontal rows vertically :

To display dynamically horizontal rows vertically , I have used the technique of dynamic unpivoting (using Xquery & nodes() method )
and then dynamic pivoting .

Below code block will transform resultset 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


Revert to this revision