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
The multiple rows for numeric_value for the same id and entry_date I can
filter out as it makes no sense to have different values on the same day
(as id 4), but the problem is with the missing term_text values per id and
date (as id 3)
I can do the above transformation fine if there are the 3 term-text values
(weight, height and bmi) per id and entry_date with a self join:
select n1.id, n1.entry_date, n1.numeric_value, n2.numeric_value,
n3.numeric_value from num_values n1
inner join num_values n2 on(n1.id = n2.id and n1.entry_date = n2.entry_date)
inner join num_values n3 on(n1.id = n3.id and n1.entry_date = n3.entry_date)
where
n1.term_text = 'Weight' and
n2.term_text = 'Weight' and
n3.term_text = 'BMI'
But this misses out on id 3 in the mentioned example data.
Any suggestion how best to do this?
RBS
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
