locked
Reverse Pivot Query RRS feed

  • Question

  • Hi Everyone,

    I run into a problem and need some help.  I'm working with an existing SQL table and need to transpose some data that are in different columns into rows, almost like a reverse pivot table.  For example, see the below data that are in columns and what  result I expect.  Anyone help would be really appreciated.

    Tuesday, October 8, 2013 2:33 AM

Answers

All replies

  • try UNPIVOT,

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

    http://blog.sqlauthority.com/2008/05/29/sql-server-unpivot-table-example/


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

    • Marked as answer by zXSwordXz Wednesday, October 9, 2013 2:51 PM
    Tuesday, October 8, 2013 2:41 AM
  • something like below

    SELECT t.COMPANY,t1.dt AS [Date],t1.Qtr1,t1.Qtr2
    FROM TableName t
    CROSS APPLY (VALUES (t.Date1,QTR1_1,QTR2_1),
                        (t.Date2,QTR1_2,QTR2_2),
                        (t.Date3,QTR1_3,QTR2_3),
                        (t.Date4,QTR1_4,QTR2_4)
                 )t1 (Dt,Qtr1,Qtr2)
    

    • Marked as answer by zXSwordXz Wednesday, October 9, 2013 2:51 PM
    Tuesday, October 8, 2013 2:54 AM
  • Refer the below sample,

    DECLARE @TEMP TABLE(COMPANY VARCHAR(10),DATE1 DATETIME,DATE2 DATETIME,QTR1_1 INT,QTR1_2 INT)
    INSERT INTO @TEMP VALUES('ABC','1/1/2013','4/1/2013',4,5)
    INSERT INTO @TEMP VALUES('CDE','1/1/2013','4/1/2013',2,3)
    SELECT COMPANY,[DATE],QTR1_1,QTR1_2 FROM @TEMP
    UNPIVOT
    ([DATE] FOR DATEVAL IN (DATE1,DATE2)) UPVT


    Regards, RSingh

    Tuesday, October 8, 2013 4:31 AM