Resources For IT Professionals
Post an article
Wikis - 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
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  T-SQL: Display Horizontal Rows Vertically

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


, , , , , , [Edit tags]
Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 14 Sep 2013 2:10 AM

    Maheshkumar S Tiwari edited Revision 3. Comment: corrected typo error,corrected tags and minor edit.  

  • 13 Sep 2013 11:17 PM

    Ed Price - MSFT edited Revision 2. Comment: code

  • 13 Sep 2013 11:14 PM

    Ed Price - MSFT edited Revision 1. Comment: code

  • 13 Sep 2013 11:14 PM

    Ed Price - MSFT edited Original. Comment: Tags, title, white space

Page 1 of 1 (4 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 13 Sep 2013 11:14 PM

    Ed Price - MSFT edited Revision 1. Comment: code

  • Posted by
    on 13 Sep 2013 11:14 PM

    Ed Price - MSFT edited Original. Comment: Tags, title, white space

  • Posted by
    on 13 Sep 2013 11:17 PM

    Ed Price - MSFT edited Revision 2. Comment: code

  • Posted by
    on 13 Sep 2013 11:40 PM

    Hello Ed,

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

  • Posted by
    on 14 Sep 2013 2:10 AM

    Maheshkumar S Tiwari edited Revision 3. Comment: corrected typo error,corrected tags and minor edit.  

  • Posted by
    on 17 Sep 2013 11:04 AM

    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

Page 1 of 1 (6 items)