title: Group column items from SQLite (Android)
body: <p>I have a series of listviews that are generated from data stored in an SQLite database on the phone. Lets say the table looks like this:</p>
  
  <pre><code>__________________________________________
  |   ID   |     X    |    Y    |     Z    | 
  |________|__________|_________|__________| 
  |   1    |   ABC    |   123   |   B94    | 
  |   2    |   ABC    |   234   |   C33    | 
  |   3    |   DEF    |   567   |   N72    | 
  |   4    |   DEF    |   789   |   K12    |  
  |________|__________|_________|__________|...
  </code></pre>
  
  <p>I want to create a listview from column 'X', and when clicked, opens up the data in column 'Y' that's associated with it's row in a new listview. The tricky part is that I want to group the same items in each column together in their listview. In this example, the first listview would have only two items "ABC; DEF" and when "ABC" is clicked it will display "123; 234" items in the next listview.</p>
  
  <p>Heres my code (Yes, I modified most of it from tutorials!):</p>
  
  <p><strong>listviewactivity.java</strong></p>
  
  <pre><code>public class listviewactivity extends Activity {    
  private SQLiteAdapter mySQLiteAdapter;
   ListView listContent;   
   SimpleCursorAdapter cursorAdapter;
   Cursor cursor;
  
  @Override
  protected void onCreate(Bundle savedInstanceState) {
      // TODO Auto-generated method stub
      super.onCreate(savedInstanceState);
      requestWindowFeature(Window.FEATURE_NO_TITLE);
      setContentView(R.layout.expenses);
      setRequestedOrientation(ActivityInfo.SCREEN_ORIENTATION_PORTRAIT);
  
      listContent = (ListView)findViewById(R.id.contentlist);
      mySQLiteAdapter = new SQLiteAdapter(this);
      mySQLiteAdapter.openToWrite();        
  
      cursor = mySQLiteAdapter.queueAll();
      String[] from = new String[]{SQLiteAdapter.KEY_ID, SQLiteAdapter.KEY_CONTENT1, SQLiteAdapter.KEY_CONTENT2};
      int[] to = new int[]{R.id.id, R.id.text1, R.id.text2};
      cursorAdapter =
       new SimpleCursorAdapter(this, R.layout.row, cursor, from, to);
      listContent.setAdapter(cursorAdapter);    
  
  
  }
  
   @Override
   protected void onDestroy() {
    // TODO Auto-generated method stub
    super.onDestroy();
    mySQLiteAdapter.close();
   }
  }
  </code></pre>
  
  <p><strong>SQLiteAdapter.java</strong></p>
  
  <pre><code>public class SQLiteAdapter {
  public static final String MYDATABASE_NAME = "MY_DATABASE";
  public static final String MYDATABASE_TABLE = "MY_TABLE";
  public static final int MYDATABASE_VERSION = 1;
  public static final String KEY_ID = "_id";
  public static final String KEY_CONTENT1 = "Content1";
  public static final String KEY_CONTENT2 = "Content2";
  public static final String KEY_CONTENT3 = "Content3"; 
  public static final String KEY_CONTENT4 = "Content4"; 
  public static final String KEY_CONTENT5 = "Content5"; 
  public static final String KEY_CONTENT6 = "Content6"; 
  public static final String KEY_CONTENT7 = "Content7"; 
  public static final String KEY_CONTENT8 = "Content8";
  
  private static final String SCRIPT_CREATE_DATABASE =
   "create table " + MYDATABASE_TABLE + " ("
   + KEY_ID + " integer primary key autoincrement, "
   + KEY_CONTENT1 + " text not null, "
   + KEY_CONTENT2 + " text not null,"  
   + KEY_CONTENT3 + " text not null, "
   + KEY_CONTENT4 + " text not null,"   
   + KEY_CONTENT5 + " text not null, "
   + KEY_CONTENT6 + " text not null,"   
   + KEY_CONTENT7 + " text not null,"  
   + KEY_CONTENT8 + " text not null);"; 
  
  private SQLiteHelper sqLiteHelper;
  private SQLiteDatabase sqLiteDatabase;
  private Context context;
  public SQLiteAdapter(Context c){
  
   context = c;
  
  } 
  
  public SQLiteAdapter openToRead() throws android.database.SQLException {
   sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
   sqLiteDatabase = sqLiteHelper.getReadableDatabase();
   return this; 
  
  } 
  
  public SQLiteAdapter openToWrite() throws android.database.SQLException {
   sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);
   sqLiteDatabase = sqLiteHelper.getWritableDatabase();
   return this; 
  
  }
  
  public void close(){
  sqLiteHelper.close();
  } 
  
  public long insert(String content1, String content2, String content3,
                  String content4, String content5, String content6, 
                             String content7, String content8){
  
   ContentValues contentValues = new ContentValues();
   contentValues.put(KEY_CONTENT1, content1);
   contentValues.put(KEY_CONTENT2, content2);  
   contentValues.put(KEY_CONTENT3, content3);  
   contentValues.put(KEY_CONTENT4, content4);  
   contentValues.put(KEY_CONTENT5, content5);  
   contentValues.put(KEY_CONTENT6, content6);  
   contentValues.put(KEY_CONTENT7, content7);
   contentValues.put(KEY_CONTENT8, content8);
  
   return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues);
  } 
  
  public int deleteAll(){
   return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null);
  }
  public Cursor queueAll(){
   String[] columns = new String[]{KEY_ID, KEY_CONTENT1, KEY_CONTENT2, KEY_CONTENT3,
                                KEY_CONTENT4, KEY_CONTENT5, KEY_CONTENT6, 
                                          KEY_CONTENT7, KEY_CONTENT8};
  
   Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns,
   null, null, null, null, null, null);
     return cursor;
  
  } 
  
   public class SQLiteHelper extends SQLiteOpenHelper {
  public SQLiteHelper(Context context, String name,
  CursorFactory factory, int version) {
  super(context, name, factory, version);
  
  }
  
  @Override
  public void onCreate(SQLiteDatabase db) {
  // TODO Auto-generated method stub
  db.execSQL(SCRIPT_CREATE_DATABASE);
  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   // TODO Auto-generated method stub   
      }
  
    } 
  
  }
  </code></pre>
  
  <p>So this just basically stores a bunch of information in a table that is loaded with a cursor into a listview. If anyone can help me with my OnItemClickListener or with grouping the column items together, that would be amazing!</p>