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