none
Transpose rows into column

    Question

  • I have a table, with a field called age. The value in this age field is

     

    2
    4
    15
    and so on...

    I need these converted into columns eg

    field 1 =2, field2 =4 , field 3= 15  and so on...
    How do I tranpsoe these rows into Columns in SQL?

    Wednesday, April 11, 2007 9:35 PM

Answers

All replies

  • If you are using SQL 2005, you can use the new PIVOT keyword. Check it out in Books Online.

     

    SQL 2000 presents a more challenging situation.

     

    Check out this thread for ideas for both SQL 2000 and SQL 2005.

    Wednesday, April 11, 2007 10:28 PM
  • I am using SQL 2000
    Wednesday, April 11, 2007 10:30 PM
  • Check out the thread previously indicated.
    Thursday, April 12, 2007 12:40 AM
  • The following example may help you..

     

    Code Snippet

    Create Table #Table
       (
         Id int,
         Age int
       )

    Insert Into #Table Values(1,10)
      Insert Into #Table Values(1,15)
      Insert Into #Table Values(1,24)

    Insert Into #Table Values(2,12)
      Insert Into #Table Values(2,12)
      Insert Into #Table Values(2,33)


      Select *,identity(int,1,1) Rowid Into #Temptable From #Table Order By Id,Age


      Select F1.ID,F1.Age as Field1, F2.Age as Field2, F3.Age as Field3 From
               #Temptable F1
                         Join #TempTable F2 on F1.Id=F2.ID and F1.Rowid=F2.RowId - 1
                                    Join #Temptable F3 On F2.ID=F3.Id and F2.Rowid = F3.RowId-1

     

     

    Thursday, April 12, 2007 4:50 AM
  • Will try this out.
    Tuesday, April 17, 2007 12:53 PM
  • If you are still looking for a solution, you must not have checked out the previous thread I was referring to.

     

    So, you may wish to check these out for your solution:

     

    Pivot Tables - How to rotate a table in SQL Server
    http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

    Pivot Tables -Dynamic Cross-Tabs
    http://www.sqlteam.com/item.asp?ItemID=2955

     

    Tuesday, April 17, 2007 2:58 PM
  • Hi all,

    I have a question regarding this thread.
    Here is my problem.
    I have table 'Code'.

    Select * from Code.Here is the result.

     

    TABLE_CODE

    USER_VALUES

    NOTES

    A01

    1

    Note1

    A02

    2

    Note2

    B01

    3

    Note3

     

    and my problem is : I want to create a query, so the result could looks like this :

     

    A01

    1

    Note1

    A02

    2

    Note2

    B01

    3

    Note3

     

    Do you guys have any suggestions for this case? Please advice.

     

    Thanks in advance

    Vera



    Thursday, November 05, 2009 3:18 AM
  • Vera, I suggest you start a new thread.
    Abdallah, PMP, ITIL, MCTS
    Thursday, November 05, 2009 4:05 AM
  • ok Abdshall. Thanks.
    Thursday, November 05, 2009 7:39 AM
  • The link for the thread no longer works.
    Wednesday, November 28, 2012 2:17 PM
  • This thread is great but what if I have an undefined number of possible 'row values' to transpose to a column?

    Example:

    Book1 - Publisher1 - Reader1
    Book1 - Publisher1 - Reader2
    Book2 - Publisher2 - Reader1
    Book2 - Publisher2 - Reader3
    Book2 - Publisher2 - Reader2
    Book2 - Publisher2 - Reader4
    Book3 - Publisher3 - Reader2
    Book4 - Publisher4 - Reader5
    Book4 - Publisher4 - Reader6

    And I want to see

    Book1 - Publisher1 - Reader1 - Reader2
    Book2 - Publisher2 - Reader1 - Reader3 - Reader2 - Reader3
    Book3 - Publisher3 - Reader2
    Book4 - Publisher4 - Reader5 - Reader6

    So for each book we don't know how many readers there will be, these could be sorted or unsorted

    AND we don't have SQLServer 2005, just plain SQL.

    Thanks!

    Wednesday, November 28, 2012 2:24 PM
  • CREATE TABLE DataSet(
    Year int, Quarter int, Amount float)
    GO


    INSERT INTO DataSet(Year, Quarter, Amount)
    VALUES(1995, 1, 125000.90)
    INSERT INTO DataSet(Year, Quarter, Amount)
    VALUES(1995, 2, 136000.75)
    INSERT INTO DataSet(Year, Quarter, Amount)
    VALUES(1995, 3, 212000.34)
    INSERT INTO DataSet(Year, Quarter, Amount)
    VALUES(1995, 4, 328000.82)
    INSERT INTO DataSet(Year, Quarter, Amount)
    VALUES(1996, 3, 728000.35)
    INSERT INTO DataSet(Year, Quarter, Amount)
    VALUES(1996, 2, 422000.13)
    INSERT INTO DataSet(Year, Quarter, Amount)
    VALUES(1996, 1, 328000.82)

     

    Select * From DataSet

     

    SELECT YEAR,
           Q1= ISNULL((SELECT AMOUNT FROM DataSet WHERE QUARTER = 1 AND YEAR =
      Q.YEAR),0),
           Q2= ISNULL((SELECT AMOUNT FROM DataSet WHERE QUARTER = 2 AND YEAR =
      Q.YEAR),0),
           Q3= ISNULL((SELECT AMOUNT FROM DataSet WHERE QUARTER = 3 AND YEAR =
      Q.YEAR),0),
           Q4= ISNULL((SELECT AMOUNT FROM DataSet WHERE QUARTER = 4 AND YEAR =
      Q.YEAR),0)
         FROM DataSet Q
         GROUP BY YEAR

     

    Select year=q.year,
    SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
    SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
    SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
    SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
    FROM DataSet q
    GROUP BY year


    Ryan Shuell

    Wednesday, January 02, 2013 4:01 AM
  • If the data in the column on which you want to do pivot is not static, then you would need dynamic pivot.

    Refer below links for dynamic pivot

    http://itdeveloperzone.blogspot.in/2011/11/dynamic-pivot-in-sql-server.html

    http://itdeveloperzone.blogspot.in/2011/01/pivot-with-dynamic-columns.html


    Sandeep Mittal | My Blog - IT Developer Zone

    Wednesday, January 02, 2013 5:18 AM