If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > horizontal to vertical array?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,521
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,417
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On