Post reply

Concatenate three columns but not when the column contains 'Value'

  • November 13, 2008 at 11:19 am

    #383379

    I need to concatenate at least three columns from a row but if the value of the column is 'Please Specify', then don't concatenate that one.

    So for example, the data is like this:

    ID Level1 Level2 Level3

    1 Incident Billing Labels

    2 Service FuelCast Access Point

    3 Service Scoring Digits PLEASE SPECIFY

    4 Incident PLEASE SPECIFY PLEASE SPECIFY

    I need the data to come back like this:

    ID Levels

    1 Incident, Billing, Labels

    2 Service, FuelCast, Access Point

    3 Service, Scoring Digits

    4 Incident

    I can't figure out how to do this without getting trailing and additional commas. I appreciate any help you can give me.

  • November 13, 2008 at 11:42 am

    #898236

    Is the list of columns to be concatenated dynamic, or is it a fixed list? (IE. it will only ever be columns A,B,C,D,E, and you only want to see the data if they are not equal to 'Please Specify')

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • November 13, 2008 at 11:43 am

    #898238

    SELECT

    CASE WHEN LEVEL1 = 'PLEASE SPECIFY' THEN '' ELSE LEVEL1 END +

    CASE WHEN LEVEL2 = 'PLEASE SPECIFY' THEN '' ELSE ',' + LEVEL2 END +

    CASE WHEN LEVEL3 = 'PLEASE SPECIFY' THEN '' ELSE ',' + LEVEL3 END AS CONCATDATA

    FROM YOURTABLE

    you might need to fiddle with some additional logic if the field can be null, because of the commas, but if the data is always not null, that should work.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 13, 2008 at 12:46 pm

    #898256

    Lowell-

    Thanks so much for your post. I don't know why I decided to make this so much harder than it actually is. Thanks again!

    Liz

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply