Display horizontal rows vertically SQL Server 2008 R2

Question

I ran the following query 

SELECT EMPID,ENAME,SALARY,DEPARTMENT from EMPLOYEE

and the result was this

EMPID     ENAME    SALARY   DEPARTMENT
---------------------------------------------
01        TEST1    2000     A/C
02        TEST2    3000     SALES

but i want to show it like this 

EMPID       01      02

ENAME       TEST1   TEST2

SALARY      2000    3000

DEPARTMENT  A/C     SALES

Answer 1

Try UNPIVOT in sql server,

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

You need a pivot table for this to work but can you tell us why you are looking to display the information in this way to begin with?

Answer 3

Hi,

You should have posted it on T-SQL forumn , try like below :

DECLARE @EMPLOYEE TABLE (EMPID INT,ENAME VARCHAR(20),SALARY INT,DEPARTMENT VARCHAR(20))
INSERT @EMPLOYEE SELECT 01,'TEST1',2000,'A/C'
INSERT @EMPLOYEE SELECT 02,'TEST2',3000,'SALES' 
DECLARE @Xmldata XML = (SELECT * FROM @EMPLOYEE FOR XML PATH('') )  
SELECT * INTO ##temp FROM (
SELECT 
dense_rank()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
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 sp_executesql @query;
--PRINT @query
--DROP TABLE ##temp

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 4

this is some what more relevant and simple! found in another forum.
CREATE TABLE #EMPLOYEE
  (
     EMPID      VARCHAR(50) NOT NULL,
     ENAME      VARCHAR(50) NOT NULL,
     SALARY     VARCHAR(50) NOT NULL,
     DEPARTMENT VARCHAR(50) NOT NULL
  )

INSERT INTO #EMPLOYEE
            (EMPID,
             ENAME,
             SALARY,
             DEPARTMENT)
VALUES     ('01',
            'TEST1',
            '2000',
            'A/C')

INSERT INTO #EMPLOYEE
            (EMPID,
             ENAME,
             SALARY,
             DEPARTMENT)
VALUES     ('02',
            'TEST2',
            '3000',
            'Sales')
 
        
--INSERT INTO #EMPLOYEE (EMPID, ENAME,SALARY,DEPARTMENT)
--        VALUES('03', 'TEST3', '3003','Extra')
---- select * from #EMPLOYEE
        
 ;with mycte
 as
 (
SELECT EMPID,col,val
FROM   (SELECT EMPID, ENAME,SALARY,DEPARTMENT
        FROM   #EMPLOYEE
) AS src1
UNPIVOT (val FOR col
IN ( [ENAME],[SALARY],[DEPARTMENT])) AS unpvt
 )
 
SELECT  col as [EMPID],[01],[02]
FROM   (SELECT EMPID,col,val
        FROM   mycte) AS src2 PIVOT
( Max(val) FOR EMPID IN ([01], [02], [03])) AS pvt

Answer 5

but problem with above query is that it is for fixed number of columns, as indicated by below line

SELECT col as [EMPID],[01],[02]

but i want to make it generic for any number of columns

also all columns have one heading as "Columns" rather than EMPID and values as Value1,Value2,Value3,Value4 ...

Another problem is that all columns must have same same datatype otherwise they wont get displayed but give an error.

can any one help me ?

Answer 6

but problem with above query is that it is for fixed number of columns, as indicated by below line

SELECT col as [EMPID],[01],[02]

but i want to make it generic for any number of columns

also all columns have one heading as "Columns" rather than EMPID and values as Value1,Value2,Value3,Value4 ...

Another problem is that all columns must have same same datatype otherwise they wont get displayed but give an error.

can any one help me ?

Hi,

Code solution in my previous post will work and it is dynamic , please try it.

---

sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

Answer 7

The only issue with the dynamic solution (which I am unfortunately hitting) is that a special character in the column name makes the xml path blow up.  I thought I had a solution where by I forced the column names to be more xml compliant.  The issue I'm facing now is that somewhere in my code, string truncation is happening and is therefore breaking the sql:

declare @xmltemplate nvarchar(max) = N'
	delete #fields;
	insert #fields
	select replace(replace(replace(replace(stuff((
		select '','' + column_name + '' ['' + replace(column_name, ''#'', ''.23'') + '']''
	from &db&.INFORMATION_SCHEMA.columns
	where table_name = ''&tblname&''
	for xml path('''')),1,1,''''), ''&#x0d;'', ''''), ''&gt;'', ''>''), ''&lt;'', ''<''), ''&amp;'', ''&'');',

My code is dynamic because I have multiple databases and tables I need to run this for.  When I put it into the rest of the sql to build the vertical table, it blows up when I have a large number of columns in a table:

@sqltemplate_top nvarchar(max) = N'
	declare @xml nvarchar(max),
		@intdoc int;
	delete #temp
	select @xml = (select ',
@sqltemplate_bottom nvarchar(max) = N'
	from &tblname&
	where acctno = ''&acctno&''
	for xml path(''''),root(''row''));
exec sp_xml_preparedocument @intdoc output, @xml;
insert #temp 
select *
from openxml(@intdoc,''row'');
exec sp_xml_removedocument @intdoc;
	
insert #fieldvalues
select ''&tbltype&'', replace(t1.localname, ''.23'', ''#'') field, t2.text value
from #temp t1
	join #temp t2
		on t1.parentid = 0
			and t1.id = t2.parentid;


select @xml = replace(replace(@xmltemplate,'&db&', @db), '&tblname&', 'subtable')

exec sp_executesql @xml

select @sqlstatement = replace(replace(replace(@sqltemplate_top, '&tblname&', @db + '.' + @owner + '.subtable'), '&acctno&', @acctno), '&tbltype&', 'subtable') + fields + 
replace(replace(replace(@sqltemplate_bottom, '&tblname&', @db + '.' + @owner + '.subtable'), '&acctno&', @acctno), '&tbltype&', 'subtable')
from #fields;

select *
from #fields
for xml path('');
exec sp_executesql @sqlstatement; --Blows up because the field names string got truncated and makes the sql invalid, but only for a large number of columns.

Any thoughts?

Answer 8

I've come up with a partial patch for my issue.  Since the string of fieldname [fieldname] was too long (and I still don't understand why, I wasn't anywhere close to the 1 GB limit for nvarchar), I broke that part up into getting all the field names that don't need the [] around them so it was just fieldname and then pulling the exceptions.  This made the string considerably smaller and let it pass on through.

I suppose a question (probably not here) is, why is SQL truncating the string that is supposed to be able to be really big?