On Fri, Jul 9, 2010 at 2:35 PM, Peng Yu <pengyu...@gmail.com> wrote:
> 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
>
Use a programming language.
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
