Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 16th, 2003, 11:20 PM
Registered User
 
Join Date: Jul 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default merging 2 columns in the same table

I have to merge together the city and state columns in my table can anyone help me with this I know it sounds simple but for some reason I can't figure it out

 
Old July 16th, 2003, 11:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Why do you want to merge the columns? This violates first normal form.

Anway, the concatination operation in SQL is +, eg:
SELECT City + State AS Location
FROM MyTable

regards
David Cameron
 
Old July 17th, 2003, 03:06 AM
Authorized User
 
Join Date: Jun 2003
Posts: 87
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi

And if you want to put a comma or space between them for display purposes.....

SELECT City + ', ' + State AS Location
FROM MyTable

Regards

Nickie
 
Old July 17th, 2003, 08:13 AM
Authorized User
 
Join Date: Jun 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure if you're trying to just run a query that returns a result set with city and state together or if you are actually trying to update your table with the merged column.

If the latter is the case you could do this:

ALTER TABLE MyTable ADD COLUMN location varchar(50)
GO
UPDATE MyTable SET location = city + ', ' + state
ALTER TABLE MyTable DROP COLUMN city
ALTER TABLE MyTable DROP COLUMN state
GO

As David said however, this would cause your data to not be atomic (every column holds only one piece of data) and there probably isn't a good reason to do it. But maybe...

-Mike





Similar Threads
Thread Thread Starter Forum Replies Last Post
Fixed table columns darkhalf HTML Code Clinic 1 August 27th, 2008 02:05 AM
first 10 columns in table vijayma SQL Server 2005 4 July 18th, 2007 09:08 PM
Sort Table Columns by Table Header Attribute omrieliav XSLT 4 June 7th, 2006 01:05 AM
How to know cells in table is merging?? DorisTan VB How-To 0 January 13th, 2005 09:50 PM
Duplicating Columns on same table yuvalk SQL Language 2 May 11th, 2004 06:22 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.