SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 combine two column
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

PatDeV
Posting Yak Master

USA
197 Posts

Posted - 10/11/2005 :  10:40:12  Show Profile
Hi Need to query out the address from info table but together as (addresss, state, zip) all in one column of report.

How can i do that.

Thanks

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/11/2005 :  10:41:48  Show Profile  Visit jsmith8858's Homepage
Does your report designer allow you to concatenate columns or create expressions? What tool are you using?
Go to Top of Page

PatDeV
Posting Yak Master

USA
197 Posts

Posted - 10/11/2005 :  10:46:12  Show Profile
yeah it is just export to txt file?

Thanks
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/11/2005 :  10:56:53  Show Profile  Visit jsmith8858's Homepage
>> yeah it is just export to txt file?

I'm afraid I don't understand your question.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/11/2005 :  11:05:38  Show Profile
This perhaps?

SELECT COALESCE(addresss, '') + ',' + COALESCE(state, '') + ',' + COALESCE(zip, '')

Kristen
Go to Top of Page

PatDeV
Posting Yak Master

USA
197 Posts

Posted - 10/11/2005 :  11:26:05  Show Profile
just want to select the data that will show in address column but combine of (address1, address2, state, city, zip, country)

Thanks
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 10/11/2005 :  11:53:33  Show Profile
Must be format using T-SQL Week. Just add the COALESCE to every column like Kristen did


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(
	  CustLastName	varchar(50)
	, Prefix	varchar(3)
	, StreetNumber	varchar(15)
	, StreetName	varchar(50)
	, City 		varchar(50)
	, State 	varchar(2)
	, PostalCode 	varchar(10))
GO

INSERT INTO myTable99(CustLastName, Prefix, StreetNumber, StreetName, City, State, PostalCode)
SELECT 'Kay',   'Mr.', '213', 'Lincoln Street', 'Newark', 'NJ', '07102-2992' UNION ALL
SELECT 'Smith', 'Mr.', '123', 'Main Street',    'Boston', 'MA', '12345'
GO

CREATE VIEW myView99
AS
SELECT    Prefix + ' ' + CustLastName 	  + CHAR(13) + CHAR(10)
	+ StreetNumber + ' ' + StreetName + CHAR(13) + CHAR(10)
	+ City + ', ' + State + '  ' + PostalCode AS AdressedTo
  FROM myTable99
GO

DECLARE @cmd varchar(8000)
SELECT @cmd = 'bcp Northwind.dbo.myView99 OUT d:\myView99.csv -T -c -S<servername>'
EXEC master..xp_cmdshell @cmd
GO

SET NOCOUNT OFF
DROP VIEW myView99
DROP TABLE myTable99
GO





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000