sqlite combine column data based on count

Question

I'm not sure if this really possible to do in sqlite as I've looked around.

Here's what I have as a part of a table (NONE are PK, FK, or IDs, just regular text data):

    col1 col2 col3
    foo1 bar1 foobar1
    foo1 bar1 foobar2
    foo1 bar1 foobar3

I was able to select the count of foo1 and bar1by doing

    SELECT col1, col2, COUNT(*)
    FROM table1
    GROUP BY col1
    HAVING COUNT(*) > 1;

to find the duplicate columns, but my end goal is to combine the data in column 3 into one column, delimited by slashes i.e foobar1/foobar2/foobar3, and delete all but 1 of the selected rows so the output would be:

    col1 col2 col3
    foo1 bar1 foobar1/foobar2/foobar3

This is pretty difficult I think to do solely in SQLite, but maybe it is possible? If not, how would you do this in a a regular programming language like Java? I am doing Android programming so I am able to access the database and do queries.

I just found this, seems like exactly what you are asking for. http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — LuckyMe, Jun 12 '13 at 00:57

score 2 · Accepted Answer · answered Jun 12 '13 at 01:04

2

I'm pretty sure SQLite supports GROUP_CONCAT:

SELECT col1, col2, GROUP_CONCAT(col3) col3
FROM table1
GROUP BY col1, col2

SQL Fiddle Demo

And if you need to change the delimiter from a "," to a "/", then use:

GROUP_CONCAT(col3, '/') col3

answered Jun 12 '13 at 01:04

sgeddes

62,311
6
61
83

thanks! you really helped me out a lot. I thought it would be a lot harder than that. – John61590 Jun 12 '13 at 01:19
nice, didn't know that existed. – LuckyMe Jun 12 '13 at 02:05

sqlite combine column data based on count

1 Answers1