CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Marco Castanho
Marco Castanho

Reputation: 473

SQLite Group_Concat

I need a SQLite query for an Android App, and I'm not quite getting it. From a bit of research, I got a possible solution, but I haven't manage to make it work yet, so it probably doesn't work in SQLite. I have a table with the following schema:

DAY  | PERSON | SCORE
---------------------
  1  | PERS1  | A
  1  | PERS2  | A
  1  | PERS3  | A
  1  | PERS4  | B
  1  | PERS5  | B
  1  | PERS6  | B
  2  | PERS1  | C
  2  | PERS2  | C
  2  | PERS3  | C
  2  | PERS4  | C
  2  | PERS5  | C
  2  | PERS6  | C

I need a query that gives me the concatenations of every PERSON with the same scores for each day, with the result of the query being presented in the following format:

DAY | RES_A             | RES_B             | RES_C
---------------------------------------------------------------------------------
1   | PERS1+PERS2+PERS3 | PERS4+PERS5+PERS6 | NULL
2   | NULL              | NULL              | PERS1+PERS2+PERS3+PERS4+PERS5+PERS6

There's only 3 types of possible results, so the query will always return the same number of columns. The solution I've been trying for the 'RES_A' column (for example) is this:

GROUP_CONCAT(IF(SCORE = 'A', CONCAT('+', PERSON), '') SEPARATOR '') AS 'RES_A'

But Java says "E/SQLiteLog: (1) near "SEPARATOR": syntax error", so I guess the keyword 'SEPARATOR' is not valid for SQLite.

Can you help me with this query?

Thanks

Upvotes: 0

Views: 822

Answers (2)

CL.
CL.

Reputation: 180280

Use CASE expressions and the second parameter of group_concat():

SELECT Day,
       group_concat(CASE Score WHEN 'A' THEN Person END, '+') AS Res_A,
       group_concat(CASE Score WHEN 'B' THEN Person END, '+') AS Res_B,
       group_concat(CASE Score WHEN 'C' THEN Person END, '+') AS Res_C
FROM MyTable
GROUP BY Day;

Upvotes: 2

Vidya Sagar
Vidya Sagar

Reputation: 180

Try this

SELECT 
    DAY,
    group_concat(concat) AS RES_A
FROM (
    SELECT
        DAY,
        CASE 
            WHEN SCORE IS 'A' THEN PERSON
        END AS concat
    FROM (YOUR_TABLE_NAME)
)
GROUP BY DAY

Upvotes: 0

Related Questions