June 30, 2020 at 3:42 pm
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?
June 30, 2020 at 4:16 pm
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.
🙂
June 30, 2020 at 5:08 pm
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.
June 30, 2020 at 5:29 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply