Thanks Keith and Igor, that works both fine indeed. Should know this by know.
RBS On Wed, Oct 23, 2019 at 6:27 PM Igor Tandetnik <i...@tandetnik.org> wrote: > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
