CODEWITHSUNDEEP
androidsqlite
Krsnik195
Krsnik195

Reputation: 79

Is it possible to split value from a column to rows when query in SQLite?

From the Table A below, I want to split value from column Color and generate new row with it. My desired result is shown in Table Result below.

Table A

Animal | Gender | Color
-------|--------|--------------------- 
 Cat   |  Male  | White, Black, Brown
 Cat   | Female | White 
 Dog   |  Male  | Black, Yellow 
 Dog   | Female | Black

Table Result

Animal | Gender | Color
-------|--------|--------------------- 
 Cat   |  Male  | White 
 Cat   |  Male  | Black 
 Cat   |  Male  | Brown 
 Cat   | Female | White 
 Dog   |  Male  | Black 
 Dog   |  Male  | Yellow 
 Dog   | Female | Black

How can I achieve this with SQLite or are there any other suggestions?

Upvotes: 1

Views: 282

Answers (1)

Vickyexpert
Vickyexpert

Reputation: 3171

Let's say you are getting each record as a String as I have mentioned below and from there you can split all data as below:

Create One Class Which Contains All Properties like Animal, Color and Gender as Below

  class AnimalData         // You can give any name 
  {
      String strAnimal, strGender, strColor;

      public AnimalData(String strAnimal, String strGender, String strColor)
      {
          this.strAnimal = strAnimal;
          this.strGender = strGender;
          this.strColor = strColor;
      }

      public String getStrAnimal()
      {
          return strAnimal;
      }

      public String getStrGender()
      {
          return strGender;
      }

      public String getStrColor()
      {
          return strColor;
      }
  }

Now currently I am using 3 static variable which contains data as above you get from database, you need to use them instead of these below variable

    String strAnimalData[] = {"Cat","Cat","Dog","Dog"};
    String strGenderData[] = {"Male","Female","Male","Female"};
    String strColorData[] = {"White,Black,Brown","White","Black,Yellow","Black"};

Now I am using one method which will give me result in list from above values

public List<AnimalData> getAllAnimalData()
{
      List<AnimalData> strAllAnimalData = new ArrayList<>();

      for(int i = 0; i < strAnimalData.length; i++)
      {
          if(strAnimalData[i].indexOf(",") > 0)
          {
              String strSubAnimalData[] = strAnimalData[i].split(",");

              for(int j = 0; j < strSubAnimalData.length; j++)
              {
                  AnimalData ad = new AnimalData(strSubAnimalData[j], strGenderData[i], strColorData[i]);
                  strAllAnimalData.add(ad);
              }
          }
          else if(strGenderData[i].indexOf(",") > 0)
          {
              String strSubGenderData[] = strGenderData[i].split(",");

              for(int j = 0; j < strSubGenderData.length; j++)
              {
                  AnimalData ad = new AnimalData(strAnimalData[i], strSubGenderData[j], strColorData[i]);
                  strAllAnimalData.add(ad);
              }
          }
          else if(strColorData[i].indexOf(",") > 0)
          {
              String strSubColorData[] = strColorData[i].split(",");

              for(int j = 0; j < strSubColorData.length; j++)
              {
                  AnimalData ad = new AnimalData(strAnimalData[i], strGenderData[i], strSubColorData[j]);
                  strAllAnimalData.add(ad);
              }
          }
          else
          {
              AnimalData ad = new AnimalData(strAnimalData[i], strGenderData[i], strColorData[i]);
            strAllAnimalData.add(ad);
          }
      }

      // Below code is just for Display Data

      for(AnimalData tempData: strAllAnimalData)
      {
          System.out.println("\t" + tempData.getStrAnimal() + "\t" + tempData.getStrGender() + "\t" + tempData.getStrColor());
        System.out.println("\n");
    }
}

Note:- Currently this will work when each position only one variable has more data, like if animal has "Cat,Dog" at position 1 and Color has also "Red,Brown" at position 1 then it will only separate animal not by color, for that you need to implement same logic inside for loops.

Upvotes: 1

Related Questions