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>
>
> --
> 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
