none
Converting rows data to columns in sql server

    Question

  •  

    I have to show data as columns which I recieve them as rows from the Database. It is as follows:

    Rowid  Value

    -------------

    1           a

    2            b

    3            c



    Now I need to send it as follows:



    1      2      3

    ------------

    a      b      c

    Can anybody send me the solutions, without using Temp Tables.

    Thanks,

    Tuesday, December 30, 2008 4:58 PM

All replies

  • Hi,
    Have a look at the PIVOT clause http://msdn.microsoft.com/en-us/library/ms177410.aspx
    Tuesday, December 30, 2008 5:05 PM
  •  
    Declare @sample table(  
      Rowid int,  
      Value char);  
     
    Insert into @sample  
      Select '1','a' Union All 
      Select '2','b' Union All 
      Select '3','c' 
     
     
    --On SQL Server 2000  
    Select   
        max(case when Rowid=1 Then Value endas [1],  
        max(case when Rowid=2 Then Value endas [2],  
        max(case when Rowid=3 Then Value endas [3]  
    From 
        @sample  
     
     
    --On SQL Server 2005 & above  
    Select * from 
    @sample  
    Pivot  
    (  
        max(value) for RowId in ([1], [2], [3])  
    as PVT 

    I am Back..!
    Tuesday, December 30, 2008 5:09 PM
  • Thank you for your response. i have an issue in using the pivot tabel, i wont be having any unquie column in that recorset. Whether it is possible for me to do without unquie or some id.
    Wednesday, December 31, 2008 2:18 PM
  • Can you provide some real sample data and expected outcome. Include at least one example of data with non-unnique identifiers and how you want the result to look in that situation (and why!).
    George
    Wednesday, December 31, 2008 2:47 PM
  • Here is the sample data

    the real data comes will be

    Col1 Col2 Col 3
    Emp1 $419.63 RowA
    Emp2 $797.30 RowA
    Emp3 $1,195.95 RowA
    Emp1 $419.63 Row b
    Emp2 $797.30 Row b
    Emp3 $1,195.95 Row b


    i need to traverse like this

    Row A Row B
    emp 1 $419.63 $419.63
    emp 2 $797.30 $797.30
    emp 3 $1,195.95 $1,195.95
    Wednesday, December 31, 2008 3:08 PM
  • You can pivot that!
    DECLARE @t table (  
       col1 char(4)  
     , col2 money  
     , col3 char(4)  
    )  
     
    INSERT INTO @t (col1, col2, col3)  
          SELECT 'Emp1', 419.63 , 'RowA' 
    UNION SELECT 'Emp2', 797.30 , 'RowA' 
    UNION SELECT 'Emp3', 1195.95, 'RowA' 
    UNION SELECT 'Emp1', 419.63 , 'RowB' 
    UNION SELECT 'Emp2', 797.30 , 'RowB' 
    UNION SELECT 'Emp3', 1195.95, 'RowB' 
     
    --2000  
    SELECT col1  
         , Sum(CASE WHEN col3 = 'RowA' THEN col2 ENDAs [RowA]  
         , Sum(CASE WHEN col3 = 'RowB' THEN col2 ENDAs [RowB]  
    FROM   @t  
    GROUP 
        BY col1  
     
    --2005  
    SELECT col1  
         , [RowA]  
         , [RowB]  
    FROM   @t  
    PIVOT  (  
            Sum(col2)  
            FOR col3 IN ([RowA], [RowB])  
           ) pvt 

    George
    Wednesday, December 31, 2008 3:29 PM
  • Hi,

    Can anyone help me to traverse the colums as rows. The below given is the resultset i am getting

     
    ID Emp_Name Type Sal1 Allowance Sal2
    419575 Ramesh Estimated  0.787734 0 0.492334
    419576 Suresh Estimated  0.816782 0 0.510488


    I need to traverse the same like this..

      Ramesh Suresh Type
    Sal 1 0.787734 0.816782 Estimated 
    Allowance 0 0 Estimated 
    Sal 2 0.492334 0.510488 Estimated 

    Thank you,
    Regards,
    Ramesh
    Sunday, January 11, 2009 9:27 PM
  • you have to use unpivot and pivot.

    DECLARE @t table (     
       id int     
     , emp_name varchar(25)    
     , type varchar(100)  
     , sal1 money  
     , allowance money  
     , sal2 money  
    )     
        
    INSERT INTO @t (id, emp_name, type, sal1, allowance, sal2)    
            SELECT 419575, 'Ramesh''Estimated',  0.787734, 0, 0.492334   
            UNION 
            SELECT 419576, 'Suresh''Estimated',  0.816782, 0, 0.510488   
        
        
     --Unpivot the table.  
    select SalType, [Ramesh], [Suresh], TYPE  
    from (  
     
            SELECT emp_name, type, SalType, pay  
            FROM   
               (SELECT emp_name, type, sal1, allowance, sal2  
               FROM @t) p  
            UNPIVOT  
               (pay FOR SalType IN   
                  (sal1, allowance, sal2)  
            )AS unpvt  
    ) a  
    pivot (  
        sum(pay) for emp_name in ([Ramesh], [Suresh])  
    ) pvt 

    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    • Proposed as answer by DigitalFM Thursday, February 03, 2011 3:57 PM
    Sunday, January 11, 2009 10:03 PM
  • Thanks your response.

    I am stuck with the one issue here

    this is my acutal result set

    Id Name Value
    419575 Administrative 6.99
    419576 Administrative  6.99


    i need to traverse to

      419575 419576
    Administrative 6.99 6.99


    how this can be done???

    Monday, January 12, 2009 1:06 AM
  •  
    DECLARE @t table (id int, Name varchar(25),value decimal(10,2))   
     
     
    INSERT INTO @t SELECT 419575, 'Administrative', 6.99   
     
    UNION ALL SELECT 419576, 'Administrative', 6.99   
     
    SELECT Name as ' ', [419575],[419576] FROM (SELECT id, Name, value FROM @t) src  
     
    PIVOT (MAX(Value) FOR id IN ([419575],[419576])) pvt  
     

     

    • Proposed as answer by DigitalFM Thursday, February 03, 2011 3:57 PM
    Monday, January 12, 2009 1:17 AM