On Sun, Jul 4, 2010 at 7:15 PM, P Kishor <punk.k...@gmail.com> wrote:
> On Sun, Jul 4, 2010 at 6:15 PM, Peng Yu <pengyu...@gmail.com> wrote:
>> Hi,
>>
>> I only find row-wise concatenation by not column-wise.
>>
>> For example, I have table
>>
>> x1 y1
>> x1 y2
>> x2 y3
>> x4 y4
>>
>> I want to have the second column concatenated based on the value in
>> the first column to get the new table
>> x1 y1y2
>> x2 y3y4
>>
>> Moreover, I want to have a spectator (e.g., ',') in the second column.
>> x1 y1,y2
>> x2 y3,y4
>>
>> Could you show me if it is possible to do this in sqlite3?
>
>
> sqlite> CREATE TABLE t (a, b);
> sqlite> INSERT INTO t VALUES ('x1', 'y1');
> sqlite> INSERT INTO t VALUES ('x1', 'y2');
> sqlite> INSERT INTO t VALUES ('x2', 'y3');
> sqlite> INSERT INTO t VALUES ('x4', 'y4');
> sqlite> SELECT * FROM t;
> a b
> ---------- ----------
> x1 y1
> x1 y2
> x2 y3
> x4 y4
> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
> a Group_concat(b)
> ---------- ---------------
> x1 y1,y2
> x2 y3
> x4 y4
> sqlite> UPDATE t SET a = 'x2' WHERE a = 'x4';
> sqlite> SELECT a, Group_concat(b) FROM t GROUP BY a;
> a Group_concat(b)
> ---------- ---------------
> x1 y1,y2
> x2 y3,y4
> sqlite>
Is there a way to reverse the operation done by Group_concat.
x1 y1,y2
x2 y3,y4
Suppose that I start with the above table, how to convert it to the
following table?
x1 y1
x1 y2
x2 y3
x2 y4
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
