combine two column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

combine two column

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2005-10-11 : 10:40:12

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

7423 Posts

Posted - 2005-10-11 : 10:41:48

Does your report designer allow you to concatenate columns or create expressions? What tool are you using?

PatDeV
Posting Yak Master

197 Posts

Posted - 2005-10-11 : 10:46:12

yeah it is just export to txt file?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-11 : 10:56:53

>> yeah it is just export to txt file?

I'm afraid I don't understand your question.

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 11:05:38

This perhaps?

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

Kristen

PatDeV
Posting Yak Master

197 Posts

Posted - 2005-10-11 : 11:26:05

just want to select the data that will show in address column but combine of (address1, address2, state, city, zip, country)

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-11 : 11:53:33

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources