Mapping specific field to a string

Question

I have a table in SQLite named Clients:

ID	Name	Hobbies	SubHobbies
1	Anne	sports, fashion	shoes, swimming
2	Max	eating, automative	desserts, cars

I split the Hobbies column using Common Table Expressions (with split() as) :

ID	Name	Hobbies	SubHobbies
1	Anne	sports	shoes, swimming
1	Anne	fashion	shoes, swimming
2	Max	eating	desserts, cars
2	Max	automative	desserts, cars

Now I also want to split the Subhubbies column, which I can do using CTE. The format I want is (Swimming is a subhubby of Sports, shoes-Fashion, desserts-eating and cars-automative) :

ID	Name	Hobbies	SubHobbies
1	Anne	sports	swimming
1	Anne	fashion	shoes
2	Max	eating	desserts
2	Max	automative	cars

There are many clients, hobbies and sub-hobbies in this table so I don't want to do this one by one. Any ideas for splitting and placing them in the right place with queries?

Answer 1

You need to define the Hobbies table:

CREATE TABLE Hobbies (
  `Hobby` TEXT,
  `SubHobby` TEXT
);

INSERT INTO Hobbies
  (`Hobby`, `SubHobby`)
VALUES
  ('sports', 'swimming'),
  ('fashion', 'shoes'),
  ('eating', 'desserts'),
  ('automative', 'cars');

You should have this table anyway, because otherwise your Clients table is not normalized. Then you run this query against the Clients/Hobbies tables:

SELECT c.ID, c.Name, h.Hobby, h.SubHobby
FROM Clients AS c, Hobbies AS h
WHERE instr(' ' || c.SubHobbies || ',', ' ' || h.SubHobby || ',')

Depending on your data, simple WHERE instr(c.SubHobbies, h.SubHobby) might also do just fine.