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?