Combining 8 columns into 4 based on rules

  • 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?

  • 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


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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