Joe smo Joe smo - 1 month ago 5
SQL Question

Using Sql to Manipulate data a certain way into a new table

Alright, so this is my second time ever asking a question on stackoverflow, and this one I believe is a little complex. If you need any extra information let me know and I will provide it, and thanks for all replies/answers!

Basically I want to take this existing table with columns as follows:

Dealer
Open24
Mopen
Mclosed
Topen
Tclosed
Wopen
Wclosed
THopen
THclosed
Fopen
Fclosed
SAopen
SAclosed
SUopen
SUclosed


I want to keep this table the same, but I want to make a new table using this data using sql to insert the data in this table into the new table. This new table would look as follows:

Day | Open | Closed | Dealer
Monday 0:00 9:00
Tuesday 0:00 14:00
...
Sunday 0:00 12:00


Dealer would be the foreign key to the table above. I have looked into pivoting data, but all I have seen is how to convert rows to columns. If anyone has a solution to this it would be greatly appreciated!

Answer

One method uses union all:

select Dealer, 'Monday' as dow, Mopen as "open", Mclosed as "closed"
from t
union all
select Dealer, 'Tuesday' as dow, Tuopen as "open", Tuclosed as "closed"
from t
union all
. . .

Creating another table depends on the dialect of SQL. Often create table newTable as works.