Reordering the results of an SQL query using SQLite

I have a SQLite database that models Sanskrit nouns and has tables like this: (Sorry if it is very lengthy. I've tried to cut things down to the minimum necessary to understand this problem.)

numbers:

id	number
1	singular
2	dual
3	plural

cases:

id	case
1	nominative
2	accusative
3	instrumental
4	dative
5	ablative
6	genitive
7	locative
8	vocative

nouns:

id	name
1	rAma

forms:

id	form	noun
1	rAmaH	1
2	rAmau	1
3	rAmAH	1
4	rAmam	1
5	rAmAN	1
6	rAmENa	1
7	rAmAbhyAm	1
8	rAmaiH	1
9	rAmAya	1
10	rAmebhyaH	1
11	rAmAt	1
12	rAmasya	1
13	ramayoH	1
14	rAmANAm	1
15	rAme	1
16	rAmeShu	1
17	rAma	1

• noun is a foreign key which references nouns(id)

nounforms:

id	form	case	number	noun
1	1	1	1	1
2	2	1	2	1
3	3	1	3	1
4	4	2	1	1
5	2	2	2	1
6	5	2	3	1
7	6	3	1	1
8	7	3	2	1
9	8	3	3	1
10	9	4	1	1
11	7	4	2	1
12	10	4	3	1
13	11	5	1	1
14	7	5	2	1
15	10	5	3	1
16	12	6	1	1
17	13	6	2	1
18	14	6	3	1
19	15	7	1	1
20	13	7	2	1
21	16	7	3	1
22	17	8	1	1
23	2	8	2	1
24	3	8	3	1

• form is a foreign key which references forms(id)
• case is a foreign key which references cases(id)
• number is a foreign key which references numbers(id)
• noun is a foreign key which references nouns(id)

I can get all the declensions of the noun rAma with this SQL query:

SELECT forms.form FROM forms JOIN nouns,nounforms
    WHERE forms.id = nounforms.form
        AND nounforms.noun = nouns.id
        AND noun.name = "rAma"
    GROUP BY nounforms.case, nounforms.number;

and that returns the whole noun perfectly in 24 rows:

form
rAmaH
rAmau
rAmAH
rAmam
rAmau
rAmAN
rAmENa
rAmAbhyAm
rAmaiH
rAmAya
rAmAbhyAm
rAmebhyaH
rAmAt
rAmAbhyAm
rAmebhyaH
rAmasya
ramayoH
rAmANAm
rAme
ramayoH
rAmeShu
rAma
rAmau
rAmAH

So far so good. But what I would really like is something like this:

singular	dual	plural
rAmaH	rAmau	rAmAH
rAmam	rAmau	rAmAN
rAmENa	rAmAbhyAm	rAmaiH
rAmAya	rAmAbhyAm	rAmebhyaH
rAmAt	rAmAbhyAm	rAmebhyaH
rAmasya	ramayoH	rAmANAm
rAme	ramayoH	rAmeShu
rAma	rAmau	rAmAH

i.e. 8 rows for each case with 3 columns for each number. The problem is my SQL knowledge is not quite enough to get me there. I think what I want is a view or a virtual table. Is that right? Also once that is solved, I would like to parametrize the query so I can use it for nouns other than rAma but SQLite does not I believe support stored procedures. Is that right? If so, what is the workaround?

Btw, I am aware that I can do the reordering in my application. In fact, that is what I am doing now but I would like to keep as much centralized in the database as possible so I can port to other languages/environments.

Can anyone help?

from Recent Questions - Stack Overflow https://ift.tt/3BiOZzX
https://ift.tt/eA8V8J