July 12, 2010 at 9:32 pm
Hello there
I am attempting to create 4 fields that represent lines on a postal letter.
I have 8 fields that are the data source
Freeformat
Flat
House number
Street
Box
Suburb
Region
Postcode
I need to be able to build addresses for each customer, and what each of the 4 address lines will contain depend on what is populated in the 8 data source fields.
For Example
Say we had a value in Flat, House Number, street, Region and Postcode the 4 lines would be
Line 1: Flat, House Number, Street
Line 2: Region, Postcode
Line 3: Null
Line 4: Null
Or if we had values in Freeformat, Street, Box, Suburb, Region, Postcode
It would be
Line1: Freeformat
Line2: Street
Line3: Box
Line4: Suburb, Region, Postcode
What I would like to know is how I would go about doing this. All I can think of at the moment is either:
1. Lots of Case When statements
2. Some kind of way to loop through the columns Freeformat, Street to Postcode and processing them programmatically.
Are there other ways of doing this?
July 12, 2010 at 9:51 pm
SELECT Line1 = CASE WHEN Freeformat IS NULL THEN Flat + ', ' + House + ' ' + Number + ', ' + Street
ELSE Freeformat END,
Line2 = CASE WHEN Freeformat IS NULL THEN Region + ', ' + Postcode
ELSE STREET END,
Line3 = CASE WHEN Freeformat IS NULL THEN NULL
ELSE Box END,
Line4 = CASE WHEN Freeformat IS NULL THEN NULL
ELSE Suburb + ', ' + Region + ', ' + Postcode END
FROM YourNamelessTable
If this isn't what your looking for, then I suggest reading the first link in my signature, and then posting your sample data in the format requested.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 12, 2010 at 10:00 pm
My bad, sorry about that, will get onto creating some sample data.
And yes that looks good
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply