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?