T-SQL: Display Horizontal Rows Vertically

T-SQL: Display Horizontal Rows Vertically

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 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 and 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





See Also


Sort by: Published Date | Most Recent | Most Useful
Comments
  • Hello Ed,

    Can this article be added to September T-SQL contest ? Eligible ?!!!

  • Sure, it can be added - strange question. The article is very nice, tricky problem. You need to add See Also section, though. Check any of my articles for example of how it should be added and formatted

  • Durval Ramos edited Revision 9. Comment: Add "See Also"

  • Naomi,

    His question only proves that you really are special to this Community.

    His "padawan" can only revere all your work!

Page 1 of 1 (4 items)