Answered by:
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 SALESWednesday, September 11, 2013 12:52 PM
Answers
-
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 **.
- Proposed as answer by Candy_Zhou Thursday, September 12, 2013 4:45 AM
- Marked as answer by Allen Li - MSFT Thursday, September 19, 2013 12:52 PM
Wednesday, September 11, 2013 5:56 PM -
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
- Proposed as answer by Candy_Zhou Thursday, September 12, 2013 4:45 AM
- Marked as answer by Allen Li - MSFT Thursday, September 19, 2013 12:52 PM
Thursday, September 12, 2013 4:34 AM
All replies
-
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.
Wednesday, September 11, 2013 1:08 PMAnswerer -
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?
- Edited by zcurtin608 Wednesday, September 11, 2013 3:13 PM
Wednesday, September 11, 2013 3:12 PM -
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 **.
- Proposed as answer by Candy_Zhou Thursday, September 12, 2013 4:45 AM
- Marked as answer by Allen Li - MSFT Thursday, September 19, 2013 12:52 PM
Wednesday, September 11, 2013 5:56 PM -
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
- Proposed as answer by Candy_Zhou Thursday, September 12, 2013 4:45 AM
- Marked as answer by Allen Li - MSFT Thursday, September 19, 2013 12:52 PM
Thursday, September 12, 2013 4:34 AM -
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 ?
- Edited by RazaSyed Thursday, September 12, 2013 7:16 AM
Thursday, September 12, 2013 7:13 AM -
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 **.
Saturday, September 14, 2013 4:22 AM -
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,''''), ''
'', ''''), ''>'', ''>''), ''<'', ''<''), ''&'', ''&'');',
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?Monday, December 28, 2015 10:04 PM -
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?
Tuesday, December 29, 2015 2:03 PM