Published on InterSystems Developer Community (https://community.intersystems.com)

Home > Concatenate columns in results with GROUP BY?

Question
Scott Beeson · Feb 16, 2016

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%SYSTEM69918
LOGINOTHER39


However, I need to get the data back as two columns with all but the last concatenated and delimited, more like this:

LOGIN;%SYSTEM69918
LOGIN;OTHER39

 

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.

kcnt
069918
039

 

is there a way to do this?

#Code Snippet #Databases #SQL #Caché

Source URL:https://community.intersystems.com/post/concatenate-columns-results-group