Combine multiple columns into one

I have a table Venues
ID int
Location1 char(10),
Location2 char(10),
Location3 char(10),
Location4 char(10)
and would like to have a query that returns a single column of
Locations i.e
for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Loca
tion4=Paris
I would get the following result
Locations
--
Boston
NewYork
London
Paris
Is it possible to merge the values from columns location1,location2
etc into a new column?SELECT
ID
,'Location 1 = ' + Location1 + ',Location 2 = ' + Location2 ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"michael" <michael.l.obrien@.ul.ie> wrote in message news:6dcedfaf.0404220202.1a24b6af@.postin
g.google.com...
> I have a table Venues
> ID int
> Location1 char(10),
> Location2 char(10),
> Location3 char(10),
> Location4 char(10)
> and would like to have a query that returns a single column of
> Locations i.e
> for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Lo
cation4=Paris
> I would get the following result
> Locations
> --
> Boston
> NewYork
> London
> Paris
> Is it possible to merge the values from columns location1,location2
> etc into a new column?|||On 22 Apr 2004 03:02:07 -0700, michael wrote:

>I have a table Venues
>ID int
>Location1 char(10),
>Location2 char(10),
>Location3 char(10),
>Location4 char(10)
>and would like to have a query that returns a single column of
>Locations i.e
>for the record ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Loc
ation4=Paris
>I would get the following result
>Locations
>--
>Boston
>NewYork
>London
>Paris
>Is it possible to merge the values from columns location1,location2
>etc into a new column?
SELECT Location1 AS Locations
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location2
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location3
FROM Venues
WHERE ID = 2
UNION ALL
SELECT Location4
FROM Venues
WHERE ID = 2
By the way, your design is not properly normalized. The
Venue-Locations should be in a seperate table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In response to "BTW, your design is not properly normalized..."
We really do not know what is being stored in the LOCATION columns.
If the 4 locations are somehow different - like location 1 is primary,
location 2 is secondary - then this design is fine in my book. If most of
the time the app in front of this table shows the 4 locations on one row,
then it's fine by me also. Normalization can and is often taken to way to
far a level.
I've seen "college" admin systems with 500 tables - so obsur that only the
original implementors have a clue as to what is going on.
In our K-12 student applications, we store all 4 marking period marks in one
row of a table. Each student/class has only one row, with all 4 marking
period marks within that row. In my book, they are different "entities",
thus this is properly normalized. I've had debates with other programmers
that they should be separated into a MARK table, underneath the
STUDENT/CLASS table. The STUDENT/CLASS table already typically has 50000+
rows per school per year - creating a sub-table with each marking period
mark, 200,000+ rows per year hurts my head.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.
4ax.com...
> On 22 Apr 2004 03:02:07 -0700, michael wrote:
>
ID=2,Location1=Boston,Location2=NewYork,
Location3=London,Location4=Paris[vbcol=s
eagreen]
> SELECT Location1 AS Locations
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location2
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location3
> FROM Venues
> WHERE ID = 2
> UNION ALL
> SELECT Location4
> FROM Venues
> WHERE ID = 2
>
> By the way, your design is not properly normalized. The
> Venue-Locations should be in a seperate table.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks to all for the help. Maybe I can clear up why I
have the table the way it is and apologise for not giving
enough details about what I am trying to do. I had a sit
down and figured out what I was trying to do after I
posted the first message.
My venues table is linked (VenueID) to a events table. The
venues table has a min of 30 locations i.e
location1...location30 and would like to have a sproc to
return all the non null locations for a particular event
ID without having to have at least 30 "select union"
sections with tests for null values
Here is my first draft of what I am thinking
--
--The variable have been declared @.inti=1,@.intj=2
--@.Column1 @.Column2 (both char(5)
--and have not added the null test yet
While @.intj <=35
Begin
Set @.Column1 ='Location'+ (cast(@.inti as char(2)))
Set @.Column2 ='Location'+ (cast(@.intj as char(2)))
SELECT @.Column1 Locations FROM Venues where
Venues.LocationID='200'
UNION
SELECT @.Column2 FROM Venues where Venues.LocationID='200'
Set @.inti =@.inti + 2
Set @.intj =@.intj + 2
End
--This only results in the following
Locations
Location1
Location2
Locations
Location3
Location4
Any suggestions
>--Original Message--
>In response to "BTW, your design is not properly
normalized..."
>We really do not know what is being stored in the
LOCATION columns.
>If the 4 locations are somehow different - like location
1 is primary,
>location 2 is secondary - then this design is fine in my
book. If most of
>the time the app in front of this table shows the 4
locations on one row,
>then it's fine by me also. Normalization can and is
often taken to way to
>far a level.
>I've seen "college" admin systems with 500 tables - so
obsur that only the
>original implementors have a clue as to what is going on.
>In our K-12 student applications, we store all 4 marking
period marks in one
>row of a table. Each student/class has only one row,
with all 4 marking
>period marks within that row. In my book, they are
different "entities",
>thus this is properly normalized. I've had debates with
other programmers
>that they should be separated into a MARK table,
underneath the
>STUDENT/CLASS table. The STUDENT/CLASS table already
typically has 50000+
>rows per school per year - creating a sub-table with each
marking period
>mark, 200,000+ rows per year hurts my head.
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in
message
> news:3n8f80548bs1ataiu9kunrtm5n008duuk6@.
4ax.com...
column of[vbcol=seagreen]
> ID=2,Location1=Boston,Location2=NewYork,
Location3=London,L
ocation4=Paris
location1,location2[vbcol=seagreen]
>
>.
>