create table num_values
(
id integer,
entry_date integer,
term_text text,
numeric_value Real
);
insert into num_values values (2, 40100, 'Weight', 80);
insert into num_values values (2, 40100, 'Height', 170);
insert into num_values values (2, 40100, 'BMI', 27.7);
insert into num_values values (2, 40200, 'Weight', 90);
insert into num_values values (2, 40200, 'Height', 170);
insert into num_values values (2, 40200, 'BMI', 31.1);
insert into num_values values (3, 38000, 'Weight', 86);
insert into num_values values (4, 40100, 'Weight', 66);
insert into num_values values (4, 40100, 'Height', 160);
insert into num_values values (4, 40100, 'BMI', 25.8);
insert into num_values values (4, 40100, 'Weight', 67);
.mode col
.head on
.nullvalue <NULL>
select id,
entry_date,
max(case when term_text == 'Weight' then numeric_value end) as Weight,
max(case when term_text == 'Height' then numeric_value end) as Height,
max(case when term_text == 'BMI' then numeric_value end) as BMI
from num_values
group by id, entry_date
order by id, entry_date;
id entry_date Weight Height BMI
---------- ---------- ---------- ---------- ----------
2 40100 80.0 170.0 27.7
2 40200 90.0 170.0 31.1
3 38000 86.0 <NULL> <NULL>
4 40100 67.0 160.0 25.8
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Bart Smissaert
>Sent: Wednesday, 23 October, 2019 10:29
>To: General Discussion of SQLite Database <sqlite-
>us...@mailinglists.sqlite.org>
>Subject: [sqlite] Different column items to fields (transpose)
>
>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
>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
