Could someone please help me on this.
Been searching for hours on several platforms, tried several solutions but can't figure out the exact code for the possible solutions. In other words: I can't get my job done.
Challenge: I want to put a text in my column "Reason": the reason(s) why the record in my "problem list"). But this text is depending on the values in different columns and (where applicable) is build up from several texts and (this is maybe the tricky part) I'd like to have a carriage return between these texts). In the table underneath No. SO1237 and SO1238. Usually I only have select statements, with or whithout nested select statements with inner of left outer joins, all that is pretty clear to me. Also I'm familiar with getting my data in a temp table.
Example data:
| No. | Description | Kind | Type | Completed | Reason |
|---|---|---|---|---|---|
| SO1234 | [Description of SO1234] | Sport | Medior | TRUE | |
| SO1235 | Music | Junior | TRUE | Description is empty | |
| SO1236 | [Description of SO1236] | Senior | TRUE | Kind is empty | |
| SO1237 | [Description of SO1237] | TRUE | Kind is empty | ||
| Type is empty | |||||
| SO1238 | [Description of SO1238] | Literature | FALSE | Type is empty | |
| Completed is false |
The data in the columns No., Description, Kind, Type and Completed comes from a select statement with an inner join to another table. What is the best way to fix this? Should/can I get these texts in my column Reason directly with the select statement? I would say it's easier to first get it in a temp table, so the number of records is a lot smaller and so performance is a far less risk. Subsequently I would think to do something with a cursor to loop through the records in my temp table, but I don't know how I have to fix the building of the text string in the column Reason. The number of columns to be checked is in the example above 4, but this is expected to increase, so the code should be modified as easy as possible.
I hope someone can help me on this.
Many thanks in advance.
I tried to use code with case when statements in the select from statement, but that is getting to complicated. Too much options/combinations are possible. I also tried to wright code with a cursur, but I can't figure out the syntax of that. I want a text in my column Reason that is consists of different text strings that (depending of the combination of found errors) can be empty or contains 1, 2, 3 or 4 text strings. So for based on the checks that are done, the records can have text strings a, a+b, a+c, a+b+c, a+d, etc.
You can construct the reason column from a series of concatonated case expressions. Use + CHAR(13)+CHAR(10) for line breaks. Here is a SQL-Server solution:
SELECT *,
TRIM(CASE Description WHEN '' THEN 'Description is empty' + CHAR(13)+CHAR(10) ELSE '' END
+CASE Kind WHEN '' THEN 'Kind is empty' + CHAR(13)+CHAR(10) ELSE '' END
+CASE Type WHEN '' THEN 'Type is empty' + CHAR(13)+CHAR(10) ELSE '' END
+CASE Completed WHEN 'False' THEN 'Completed is false' + CHAR(13)+CHAR(10) ELSE '' END) as
CalculatedReason
FROM ProblemList