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 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 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 Tables with DrillDown in one report

Hi NG,
i try to put 3 tables on a report who filled up by three different named
datasets (but with same structure).
All datasets are filled from the a AS-cube with the same MDX-Statement. The
difference between the datasets are the different parameters.
And here is my problem:
I start to develop the report with the designer...integrate the first
MDX-Statement and create a drillDown-Table. Everything works fine. After
that i put another table on the reportpage, bind the second dataset on it,
insert grouping to the table and so on. But the drillDown wont
work....there is no show-and-hide toggle (the +-sign)
Also...i could not copy the first table and put it on the same report...if
i do it, i get compile-errors.
Anybody here with an example which includes tow tables with drilldown who
based on the same dataset?
regards
FrankFrank Matthiesen wrote:
forget the question...i was to blind. I fixed it.
frank