Concatenate columns in results with GROUP BY?
Here is my original query:
SELECT EventType, InitiatedAt, COUNT(*) as cnt FROM HS_IHE_ATNA_Repository.Aggregation WHERE EventType = 'LOGIN' AND LocalDateTime > '2016-02-16 11:00:00' GROUP BY EventType, InitiatedAt
This gives me data like this:
LOGIN | %SYSTEM | 69918 |
LOGIN | OTHER | 39 |
However, I need to get the data back as two columns with all but the last concatenated and delimited, more like this:
LOGIN;%SYSTEM | 69918 |
LOGIN;OTHER | 39 |
I tried this:
SELECT EventType + ';' + InitiatedAt as k, COUNT(*) as cnt FROM HS_IHE_ATNA_Repository.Aggregation WHERE EventType = 'LOGIN' AND LocalDateTime > '2016-02-16 11:00:00' GROUP BY EventType, InitiatedAt
But the first column always comes back as zero.
k | cnt |
---|---|
0 | 69918 |
0 | 39 |
is there a way to do this?
You want to use the concatenate operator: ||
e.g.:
+ is the addition operator, which implicitly treats string values as 0.
Perfect! That's what I get for using too much Javascript I guess :)
There are some ways to concat values in a cloumn in Caché: CONCAT and STRING.
CONCAT concatenates two strings to return a concatenated string. You can perform exactly the same operation using the concatenate operator (||).
STRING converts one or more strings to the STRING format, and then concatenates these strings into a single string. No case transformation is performed.