Concatenate columns in results with GROUP BY?

Question

Question

Scott Beeson · Feb 16, 2016

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?

Discussion (3)1

Log in or sign up to continue

Tom Woodfin · Feb 16, 2016

You want to use the concatenate operator: ||

e.g.:

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

+ is the addition operator, which implicitly treats string values as 0.

4 0

score 0 · Answer 1 · 2016-02-16T17:12:34-05:00

Scott Beeson · Feb 16, 2016

Perfect! That's what I get for using too much Javascript I guess :)

0 0

score 0 · Answer 2 · 2016-02-17T03:49:45-05:00

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.