On 10/23/2019 12:28 PM, Bart Smissaert wrote:
Have a table created like this:create table num_values(id integer, entry_date integer, term_text text, numeric_value Real) For this problem I am only interested in the rows that have either 'Weight', 'Height' or 'BMI' in the term_text field and I can make table that have only these values. Now usually for a particular id and entry_date there are 3 rows for the above mentioned 3 values for term_text, but this is not always so. So, table date could be like this: id entry_date term_text, numeric_value ------------------------------------------------------ 2 40100 Weight 80 2 40100 Height 170 2 40100 BMI 27.7 2 40200 Weight 90 2 40200 Height 170 2 40200 BMI 31.1 3 38000 Weight 86 4 40100 Weight 66 4 40100 Height 160 4 40100 BMI 25.8 4 40100 Weight 67 I want to run a select (or table insert) to get the data like this id entry_date weight height bmi -------------------------------------------- 2 40100 80 170 27.7 2 40200 90 170 31.1 3 38000 86 4 40100 66 160 25.8
Something along these lines, perhaps: select id, entry_date, max(case term_text when 'Weight' then numeric_value else 0 end) weight, max(case term_text when 'Height' then numeric_value else 0 end) height, max(case term_text when 'BMI' then numeric_value else 0 end) bmi from num_values group by id, entry_date; -- Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
