Multiple Union

Anyone point know a more elegant solution to the following?
I have 2 tables
Events and Locations
Events structure is
EventID int
EventName varchar
EventLeader int
LocationID int
Locations consists of
LocationID int
Location1 varchar
Location2 varchar
Location3 varchar
.
.
Location20 varchar
I want to be able to get 1 column containing all locations
for a particular EventID like
Location
Boston
.
.
Seattle
NewYork
The way I found is having a SELECT location1 ..UNION
SELECT location2 all the way to SELECT location20
Is there a better way?"michael.obrien@.DelThiSul.ie" <anonymous@.discussions.microsoft.com> wrote in
message news:57ef01c42d34$2e05fc30$a501280a@.phx.gbl...
> Anyone point know a more elegant solution to the following?
> I have 2 tables
> Events and Locations
> Events structure is
> EventID int
> EventName varchar
> EventLeader int
> LocationID int
> Locations consists of
> LocationID int
> Location1 varchar
> Location2 varchar
> Location3 varchar
> .
> .
> Location20 varchar
> I want to be able to get 1 column containing all locations
> for a particular EventID like
> Location
> Boston
> .
> .
> Seattle
> NewYork
> The way I found is having a SELECT location1 ..UNION
> SELECT location2 all the way to SELECT location20
> Is there a better way?
Well you could store the locations in a seperate table, or use a
table-values UDF.
Like this:
create table location(
LocationID int,
Location1 varchar(20),
Location2 varchar(20),
Location3 varchar(20))
go
alter function get_locations(@.LocationID int)
returns @.locations table (Location varchar(20))
as
begin
declare @.Location1 varchar(20)
declare @.Location2 varchar(20)
declare @.Location3 varchar(20)
select
@.Location1 = Location1,
@.Location2 = Location2,
@.Location3 = Location3
from location
where locationID = @.LocationID
if @.Location1 is not null
insert @.locations values(@.Location1);
if @.Location2 is not null
insert @.locations values(@.Location2);
if @.Location3 is not null
insert @.locations values(@.Location3);
return;
end
go
insert location(locationid, location1,location2) values
(1,'Detroit','Houston')
select * from get_locations(1)
David

Multiple results from single column

Ok this subject sounds a bit vague, but here's my question:

I have a table containing a user id column, a column with events (like log in) and a date column (mm/dd/yy hh/mm/ss).

What i would like to do is to count the number of users who have logged into my system, sorted by month.

Example: January - 20 users
Februari - 42 users
March - 13 users etc.

Can this be done with just one SQL statement? If so, can anyone give me an example query?

Thanks!

SanderHello,

what do you think about

SELECT TO_CHAR(date_field, 'MONTH'), count(*) FROM table_name
GROUP BY TO_CHAR(date_field, 'MONTH')


if you are using an Oracle database.

Hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Hi Manfred Peter,

I'm sorry to say i don't have Oracle, i run MSSQL7. So the statement you provided won't work (i know for sure the to_char function won't work) :rolleyes:

What i have so far is:

select count(users) from table where ops='login' and date between dateA and dateB

This will return me one number from the given timeframe. I want to create a statement that will give me the number of logins over the time period of a year for each month, so 12 numbers. I can't just copy/paste the same statement 11 times...can I :confused:

Thanks!

Sander.|||Hello Sander,

of course you can, but this means 12 times parsing the sqlstatement and reqeusting the datas from the database.
There must be an equivalent to the TO_CHAR function in MSQL Server.

You just need the function that gives only the complete month of a date value (lets say this function is called MONTH(x));

Then the statement

SELECT MONTH(date_field), COUNT(*) from table
GROUP BY MONTH(date_field)

This is the better way ... in my opinion
Search the doku for such a command :)

Let me know if you have further problems.

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com