Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    8

    horizontal to vertical array?

    Hello,

    Not sure if there is any basic info you need to know...I'm running SQL Server 2008 Standard.

    I need to create a query that has data from multiple columns (Columns 1-6 with coresponding Date started and Date Completed data) displayed vertically, but also has the column name the preeceeding column to identify it...along with other data (Record number, status).

    Record Number, Status, Column Name, Date Started, DateCompleted
    1, Open, Column 1, 1/1/2012, 2/1/2012,
    2, Hold, Column 2, 1/3/2012, 3/1/2012,
    1, Open, Column 3, 2/5/2012, 4/6/2012,
    3, Closed, Column 4, 5/10/2012, 7/25/2012,
    2, Hold, Column 5, 3/9/2012, 4/1/2012,
    1, open, Column 6, 10/10/2012, 12/12/2012,


    Please reply with any question you may have. Sorry, I did the best I could to describe what I need, the formating wasn't helping either. I went with CSV, if it helps.

    As Always, any help would be greatly apprechated.
    Thank you,
    David92595

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Need sample data.
    Need expected output.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2012
    Posts
    8
    By Horizontal I mean Column 1 -6 would be normal columns in a table.
    I need to take a normal table

    Column1,Column2,Column3,Column14,Column5,Column6, Date Started, DateCompleted
    Data, Data, Data, Data, Data, Data, Data, Data,
    Data, Data, Data, Data, Data, Data, Data, Data,
    Data, Data, Data, Data, Data, Data, Data, Data,

    With data in each column and turn it into

    Column Name,Date Started, Date Completed
    Column1,data, data,
    Column2,data, data,
    Column3,data, data,
    Column4,data, data,
    Column5,data, data,
    Column6,data, data,

    Hope this helps explain thing a bit better,
    David92595

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,910
    Without the sample input and desired output, the best I can do is suggest that you review Using PIVOT and UNPIVOT to see if that helps.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2012
    Posts
    8
    You are correct I need to use the unpivot command. (THANK YOU!!!)
    from the example you gave I was able to get an example query running, but I need to add another column to it. I've listed two queries below that I need to merge so I end up with columns: TS, Step, Date_Completed, Follow_Up_Date


    Code:
    SELECT TS, Step, [Date_Completed]
    FROM 
       (SELECT TS, LG_Note_Ordered_C, SCRA_Referral_C, LG_Recvd_C, Note_Recvd_C, Bene_Chain_C
       FROM Activity_Tracking_HI) a
    UNPIVOT
       ([Date_Completed] FOR Step IN (LG_Note_Ordered_C, SCRA_Referral_C, LG_Recvd_C, Note_Recvd_C, Bene_Chain_C))AS unpvt;
    
    SELECT TS, Step, [Follow_Up_Date]
    FROM 
       (SELECT TS, LG_Note_Ordered_F, SCRA_Referral_F, LG_Recvd_F, Note_Recvd_F, Bene_Chain_F
       FROM Activity_Tracking_HI) a
    UNPIVOT
       ([Follow_Up_Date] FOR Step IN (LG_Note_Ordered_F, SCRA_Referral_F, LG_Recvd_F, Note_Recvd_F, Bene_Chain_F))AS unpvt2;
    Hope this gives more detail on what I'm looking for...
    Thanks again,
    David92595
    Last edited by Pat Phelan; 11-30-12 at 15:13. Reason: Fixed trivial formatting error

  6. #6
    Join Date
    Nov 2012
    Posts
    8

    Question

    so after most of a days work I have all but my WHERE statment completed.

    Code:
    Use TestDB
    SELECT TS as ID, Step, [Date_Completed] as [Date Completed], Step2, [Follow_Up] as [Follow up Date]
    FROM 
       (SELECT ID, LG_Note_Ordered_C as [LG Note Ordered], SCRA_Referral_C as [SCRA Referral], LG_Recvd_C as [LG Recvd], Note_Recvd_C as [Note Recvd], Bene_Chain_C as [Bene Chain], LG_Note_Ordered_F as [LG Note Ordered2], SCRA_Referral_F as [SCRA Referral2], LG_Recvd_F as[LG Recvd2], Note_Recvd_F as [Note Recvd2], Bene_Chain_F as [Bene Chain2]
       FROM Activity_Tracking_HI) Main
      
    UNPIVOT
       ([Follow_Up] FOR Step2 IN ([LG Note Ordered2], [SCRA Referral2], [LG Recvd2], [Note Recvd2], [Bene Chain2])) Followup
    
    UNPIVOT
       ([Date_Completed] FOR Step IN ([LG Note Ordered], [SCRA Referral], [LG Recvd], [Note Recvd], [Bene Chain])) Completed
    Result Set:
    ID, Step, Date Completed, Step2, Follow up Date,
    1, SCRA Referral, 11/26/2012, SCRA Referral2, 11/27/2012,
    1, LG Recvd, 3/23/2011 ,SCRA Referral2, 11/27/2012,
    1, Note Recvd, 7/10/2012 ,SCRA Referral2, 11/27/2012,
    1, Bene Chain, 11/27/2012, SCRA Referral2, 11/27/2012,
    2, LG Note Ordered, 5/11/2012 ,SCRA Referral2, 11/8/2012,
    2, SCRA Referral, 11/6/2012 ,SCRA Referral2, 11/8/2012,
    2, LG Recvd, 5/15/2012, SCRA Referral2, 11/8/2012,
    2, Note Recvd, 7/13/2012 ,SCRA Referral2, 11/8/2012,
    2, Bene Chain, 11/29/2012, SCRA Referral2, 11/8/2012,
    2, LG Note Ordered, 5/15/2012, Bene Chain2, 11/30/2012,


    I need to show only where step name = Step2 name. for example line one SCRA Referral = SCRA Referral2. I do not want to show the rest of the rows where ID = 1

    Any Idea's?
    David92595
    Last edited by David92595; 11-30-12 at 18:42.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •