Create columns from multiple SELECT statements?

chroniclemaster1 · December 23rd, 2009, 04:07 PM

I have tried a couple different DB configurations, spent time with some of my SQL friends, etc. and I can't accomplish what I'm trying to do. Is there a way to write SQL to generate a column from a Subquery? Or do I need to remodel the database?

I first tried storing my data in a table like this...

TimeslotID int Primary Key

-- identify the timeslot's date, time and location
LocationID int
TimeID int
DateID int

--and lots of information about the timeslots like...
TimeslotStatus int
TimeslotOwner int
etc.

However, when I try to create a result table, I can't find SQL that will let me return multiple column of TimeslotStatus (one column for each dateID). I've tried subqueries (but they are returning a column and so it throws errors). Someone suggested I create a UNION of SELECT statements for each column, but it doesn't seem to want to return a column of records.

Next I tried storing the data like this and I could create the HTML table...

TimeslotID int Primary Key

-- identify the timeslot's date, time and location
LocationID int
TimeID int

Date1Status int
Date2Status int
Date3Status int
etc.

However, I can't do "ANYTHING" else because I can't store any of the other information for the timeslots (or at least I can't figure out how to connect them)

So can I "save" either of these models? Or what kind of structure would allow me to 1) build my calendar AND 2) store all the information (TimeslotStatus, TimeslotOwner, etc.) for each timeslot?

gbianchi · December 28th, 2009, 10:06 AM

Can you please post an example??

The data you are supplying is confuse (at least for me).

chroniclemaster1 · December 29th, 2009, 12:51 AM

I've gotten responses on a couple of other bulletin boards saying that this is literally impossible in SQL. The query needed is relatively simple to construct but it's disallowed. I need a subquery that returns a column set which is easy to construct, however, SQL only permits scalar subqueries as part of the column list.

I'm trying to convert a database table that looks like this...

TimeslotID LocationID TimeID DateID TimeslotStatus

11 11 11
21 12 12
31 13 13
4 1 1 4 14
5 1 2 1 21
6 1 2 2 22
7 1 2 3 23
8 1 2 4 24
9 1 3 1 31

.
.
.

into this result set...

TIME Col:Date1 Col:Date2 Col:Date3 Col:Date4

Row:Time1 11 12 13 14
Row:Time2 21 22 23 24
Row:Time3 31 32 33 34
Row:Time4 41 42 43 44
Row:Time5 51 52 53 54

I'd appreciate any insights you have on creating a solution in spite of the limitations built into SQL. Right now I'm trying to just dump the database table results for one location and build a data adapter that manually formats the information. It is not going well. .NET seems to have some sticky issues interfacing with the MySQL database which is all I have to program against in this case. *sigh*

gbianchi · December 29th, 2009, 09:22 AM

Well, other people is rigth. You can't do this in a fashion way.
You can do this tricking SQL and hardcoding values, but only if the table will not change in time and the values remain always the same.

For example, this code will solve your particular example

Code:

SELECT Distinct  timeid,
(select TimeslotStatus from tabla1 t1 where t1.dateid = 1 and t1.timeid = t.timeid ) as date1,
(select TimeslotStatus from tabla1 t1 where t1.dateid = 2 and t1.timeid = t.timeid ) as date2,
(select TimeslotStatus from tabla1 t1 where t1.dateid = 3 and t1.timeid = t.timeid ) as date3,
(select TimeslotStatus from tabla1 t1 where t1.dateid = 4 and t1.timeid = t.timeid ) as date4
FROM tabla1 as t

NOTE: is coded in access, syntaxis could change a little in mysql.

If you see the query, you could build it on the fly, but you will have to check the data every time before constructing it.

If all your problem is related to how to display it on screen, then transforming it after you get it is ok.