Wednesday, October 5, 2011

sqlite3 concatenate a column by group

there is function:
group_concat(X)

group_concat(X,Y)

So we can easily from 
The group_concat() function returns a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X. A comma (",") is used as the separator if Y is omitted. The order of the concatenated elements is arbitrary.
So we can easily from say, we have table tname as 

id|attr
1|black
1|female
2|male
2|white
3|other

to by ``select id, group_concat(attr) from tname group by id'':

1|black,female
2|white,male
3|other


No comments: