Build a string based on values from other fields

  • jaykappy

    SSC Rookie

    Points: 32

    I have 10 fields that have either a 1 for true and 0 for false

    I have an 11th field that I want to create a list in based on which attribute fields have a 1 or true in it

    I need to read each record and each of the 10 fields in that record.  If the value is 1 than write the field name (or a value that I specify, if easier) into the string and then add a comma

    Att1       Att2      Att3  etc  etc

    1           0          1

    0           0          1

    Results would be built for each record and put in the String Field

    ListField

    Record 1        Att1, Att3

    Record 2        Att3

    ANY ideas?

  • Zidar

    SSChasing Mays

    Points: 651

    Without more information, It looks like the table design does not support well what you want to achieve. The best thing would be to normalize the design - instead of having 10 columns, to have 10 rows. If changing the table design is out of question for some reason, you can try something like  this:

    SELECT RecordID, ListField = (CASE WHEN Att1 = 1 THEN 'Att1' ELSE '' END ) + ',' +(CASE WHEN Att12= 1 THEN 'Att2' ELSE '' END ) + ','+ (CASE WEHN Att3=1 THEN 'Att3' ELSE '' END) + ','.......

    Each CASE statement reads specific field, and if it holds 1 returns field name. If it holds zero, it returns empty string. Then all those CASE statements are concatenated and you get desired string. Field names are hard-codded, but you write the statement only once. If you add more fields, you will need to expand the statement.

    Normalized design avoids updating statement. Instead of series of CASE -WHEN-THEN -ELSE -END statements a simple aggregate function would do the work. If you are interested, we can discuss that solution too.

    I hope this helped.

    🙂

  • fahey.jonathan

    Hall of Fame

    Points: 3567

    The  "CASE" statements above look correct, but that method will always add the commas even if there is no displayed value.  You may get a final string that looks like ",,,Attr5,,,,".  My guess is that is not what you want.

    I suggest you look at the CONCAT_WS() function.  Documentation is available here: https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver15. The CASE statements can still be used as the arguments.

  • Zidar

    SSChasing Mays

    Points: 651

    You are right about commas. Good catch and useful observation, thank you.  Comma issue be avoided by rearranging the statement, by positioning commas inside CASE. However, Concat_WS() is way better, I agree. That is the problem with typing the reply directly, without  trying it - one gets the main idea right, but miss important details

    The point I want to make that CASE is necessary, which yields awkward and ugly solution, and as we see, seldom works from the first time.  It is far from preferable solution,  normalizing would be better.   I think there is a relatively new aggregate function STRING_AGG() that works with GROUP BY, which lends itself nicely to solution of this kind of problems.  I hope that Jaykappy would be interested in normalized solution, there is much more to learn and benefit from that approach.

    🙂

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

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