How do I pull one column from an SQLite into one string?

Question

I'm looking an efficient way to do this. A way that hopefully needs one string. I want the data presented like the following:

row1
row2
row3
row4

The reason I want to try and use one string is because I may call the same query from different tables and it will have a different number of rows. One might have 10, the other 8. So I can't specifically use 8 strings when there might be a table of 10.

Is there a way I can do it so that it simply collects one columns worth of data then I can format it to be like above/

What I have at the moment does it but uses multiple strings. See below:

//select stuff
    Cursor c = db.rawQuery("SELECT * FROM table_one", null);
        c.moveToFirst();
        in1 = c.getString(c.getColumnIndex("column_one"));
        c.moveToNext();
        in2 = c.getString(c.getColumnIndex("column_one"));
        c.moveToNext();
        in3 = c.getString(c.getColumnIndex("column_one"));
        c.moveToNext();
        in4 = c.getString(c.getColumnIndex("column_one"));
        c.moveToNext();
        in5 = c.getString(c.getColumnIndex("column_one"));

    String all = in1 + "\n" + in2 + "\n" + in3 + "\n" + in4 + "\n" + in5;

Of course this code assumes there will always be 5 bits of information in the column where as it varies.

Is there a better way?

Appreciate the help.

are you asking how to transpose your table and then concat all the single values? like using group_concat(str separator "\n") ? — ROLO, Oct 23 '13 at 18:32
[How about a `for` or `while` loop?](http://stackoverflow.com/questions/10723770/whats-the-best-way-to-iterate-an-android-cursor) — MH., Oct 23 '13 at 18:33

serge · Accepted Answer · 2013-10-23T18:41:02.907

0

You can use loop and construct data like this:

Cursor c = db.rawQuery("SELECT * FROM table_one", null);
String all = "";
if (c != null) {  
    if(c.moveToFirst())
        do {
            String in = c.getString(c.getColumnIndex("column_one"));
            all += in + "\n";
        } while (c.moveToNext());
    c.close();
}

edited Oct 23 '13 at 18:41

answered Oct 23 '13 at 18:35

serge

1,590
2
26
49

This is working for me. Bit of a side question in relation, the last two cells in the column are empty, is there a way to prevent it showing as "null" in the app? – RED_ Oct 23 '13 at 18:42
Just do `if(in != null)` in front of `all += in +"\n"` – serge Oct 23 '13 at 19:13
Please consider using something like a `StringBuilder` too. In Java, strings are immutable, so every time you concatenate strings, you're creating multiple useless objects that need to be garbage collected right after the operation finishes. This may not be a big deal for small data sets, [but you're definitely going to notice it as it grows](http://stackoverflow.com/a/1532483/1029225). – MH. Oct 23 '13 at 20:28

How do I pull one column from an SQLite into one string?

1 Answers1