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