Management Studio Report, Horizontal to Vertical array?

[David92595]

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

 

[bborissov]

What you want as a result from that data you provide?

Borislav Borissov
VFP9 SP2, SQL Server

 

[litton1]

By horizontal you mean its in one column but you want to break it up into multiple columns?

Age is a consequence of experience

 

[David92595]

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

 

[simian336]

I think you are looking for

select Column1, Date Started, DateCompleted
from yourtable
union all
select Column2, Date Started, DateCompleted
from yourtable
union all
select Column3, Date Started, DateCompleted
from yourtable
union all
select Column4, Date Started, DateCompleted
from yourtable
union all
select Column5, Date Started, DateCompleted
from yourtable
union all
select Column6, Date Started, DateCompleted
from yourtable

Simi

 

[David92595]

okay, I've figured out that what I was looking for was the unpivot command. From the example I was given I can unpiviot half of what I need to do. I've listed the two queries I need to merge so I end up with columns: TS, Step, Date_Completed, Follow_Up_Date

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

 

[David92595]

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

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 for ID = 1

Any Idea's?
David92595

 

[IanWaterman]

I think the only way is to do another select

Select * from
(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
)
where step2 like step +'%'

Ian

 

[IanWaterman]

Sorry forgot to alias your pivot table

Select * from
(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
) P
where step2 like step +'%'