locked
Display horizontal rows vertically SQL Server 2008 R2 RRS feed

  • 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

    Wednesday, 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 PM
    Answerer
  • 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,''''), ''&#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?

    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