DEPARTMENT EMPID ENAME SALARY A/C 1 TEST1 2000 SALES 2 TEST2 3000
ColumnName 1 2 DEPARTMENT A/C SALES EMPID 1 2 ENAME TEST1 TEST2 SALARY 2000 3000
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
DECLARE
@EMPLOYEE
TABLE
(DEPARTMENT
VARCHAR
(20),EMPID
INT
,ENAME
(20),SALARY
)
INSERT
SELECT
'A/C'
,01,
'TEST1'
,2000
'SALES'
,02,
'TEST2'
,3000
*
FROM
@Xmldata XML = (
FOR
XML PATH(
''
) )
--Dynamic unpivoting
INTO
##
temp
(
ROW_NUMBER()OVER(PARTITION
BY
ColumnName
ORDER
ColumnValue) rn,*
i.value(
'local-name(.)'
,
'varchar(100)'
) ColumnName,
'.'
) ColumnValue
@xmldata.nodes(
'//*[text()]'
) x(i) ) tmp ) tmp1
--SELECT * FROM ##temp
--Dynamic pivoting
@Columns NVARCHAR(
MAX
),@query NVARCHAR(
@Columns = STUFF(
', '
+QUOTENAME(
CONVERT
,rn))
DISTINCT
rn
AS
T
)),1,2,
SET
@query = N
'
SELECT ColumnName,'
+ @Columns +
SELECT * FROM ##temp
) i
PIVOT
MAX(ColumnValue) FOR rn IN ('
+ @Columns
+
')
) j ;'
;
EXEC
(@query)
--PRINT @query
DROP