How to transform row data into columns? [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 8 years ago.

开发者_如何学Python Improve this question

I have this data from a sql table, but do not know how to put into the table structure below. What is the best method for converting the data to that structure?

2011/07/13  Wednesday   10:00   1       
2011/07/13  Wednesday   10:30   1       
2011/07/13  Wednesday   11:00   0   
... 
2011/07/15  Friday      10:00   1       
2011/07/15  Friday      10:30   0       
2011/07/15  Friday      11:00   0       
2011/07/15  Friday      11:30   1   
... 
2011/07/16  Saturday    09:00   0       
2011/07/16  Saturday    09:30   1       
2011/07/16  Saturday    10:00   1   
...       
2011/07/17  Sunday      10:00   1       
2011/07/17  Sunday      10:30   0       
2011/07/17  Sunday      11:00   0       
2011/07/17  Sunday      11:30   1   
...         

---

If your RDBMS doesn't support PIVOT, you could do something like this:

SELECT
  TIME,
  (SELECT FLAG FROM SCHED S WHERE DATE = '7/13/2011' AND TIME = SCHED.TIME) AS [7/13/2011],
  (SELECT FLAG FROM SCHED S WHERE DATE = '7/14/2011' AND TIME = SCHED.TIME) AS [7/14/2011]
  ... other date columns ...
FROM            
   SCHED

This is assuming your table has this structure:

CREATE TABLE SCHED
(
    [DATE] date,
    TIME char(5),
    FLAG tinyint
)

And you probably want to use the date/time functions to calculate dates relative to the current date in your subqueries rather than hard-code them like I have, but you get the idea :-)

---

Use the pivot operator (MS-SQL)

See here:
How to transform a datatable to a ReportingService-like matrix?