Simple question on formatting - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Simple question on formatting

Author

Topic

gozu
Starting Member

4 Posts

Posted - 2007-08-04 : 20:14:48

Here is my query:

SELECT departments.department_name AS "Department name", locations.STREET_ADDRESS AS "Mailing address", locations.city, locations.state_province, locations.postal_code
FROM departments, locations
WHERE departments.location_id = locations.location_id
AND locations.country_id = 'US'
ORDER BY department_name;

and here is what the output is like:

Department name                Mailing address
------------------------------ ----------------------------------------
CITY                           STATE_PROVINCE            POSTAL_CODE
------------------------------ ------------------------- ------------
Administration                 2004 Charade Rd
Seattle                        Washington                98199
IT                             2014 Jabberwocky Rd
Southlake                      Texas                     26192

But I need it to look like this:

Department Name                Mailing Address
----------------------                -----------------------------------------------------
Administration                     2004 Charade Rd, Seattle, Washington 98199
IT                                 2014 Jabberwocky Rd, Southlake, Texas 26192

How can I do this?
Thanks for your help.

PS: it doesn't matter if the displayed data is folded because of the number of lines allowed.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-04 : 20:23:13

Where did the "IT" and the Jabberwock Rd come from?

Also, on "tied" addresses, which department name do you want? In other words, why did you choose "Administration" as the one to display?

Last question would be... why are you doing this type of formatting in SQL instead of the App?

--Jeff Moden

gozu
Starting Member

4 Posts

Posted - 2007-08-04 : 21:23:24

I have edited my sample to make it more understandable.

IT is just a department name, like Administration.

The are no duplicates or anything. I'm displaying all the departments located in the US.

I'm doing this in SQL because it's homework.

nirene
Yak Posting Veteran

98 Posts

Posted - 2007-08-04 : 22:11:53

SELECT departments.department_name AS "Department name", rtrim(locations.STREET_ADDRESS)+','+rtrim(locations.city)+','+rtrim(locations.state_province)+','+rtrim(locations.postal_code) AS "Mailing address"

Just concatenate the field name and trim it.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-08-06 : 07:20:32

Then the correct answer to the "homework" problem is, "Perform formatting in the front end".

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources