Postgresql Split single row to multiple rows

Question

I'm new to postgresql. I'm getting below results from a query and now I need to split single row to obtain multiple rows. I have gone through below links, but still couldn't manage it. Please help. unpivot and PostgreSQL How to split a row into multiple rows with a single query?

Current result

id,name,sub1code,sub1level,sub1hrs,sub2code,sub2level,sub2hrs,sub3code,sub3level,sub3hrs --continue till sub15

1,Silva,CHIN,L1,12,MATH,L2,20,AGRW,L2,35

2,Perera,MATH,L3,30,ENGL,L1,10,CHIN,L2,50

What we want

id,name,subcode,sublevel,subhrs

1,Silva,CHIN,L1,12

1,Silva,MATH,L2,20

1,Silva,AGRW,L2,35

2,Perera,MATH,L3,30

2,Perera,ENGL,L1,10

2,Perera,CHIN,L2,50

Answer 1

Use union:

select id, 1 as "#", name, sub1code, sub1level, sub1hrs
from a_table
union all
select id, 2 as "#", name, sub2code, sub2level, sub2hrs
from a_table
union all
select id, 3 as "#", name, sub3code, sub3level, sub3hrs
from a_table
order by 1, 2;

 id | # |  name  | sub1code | sub1level | sub1hrs 
----+---+--------+----------+-----------+---------
  1 | 1 | Silva  | CHIN     | L1        |      12
  1 | 2 | Silva  | MATH     | L2        |      20
  1 | 3 | Silva  | AGRW     | L2        |      35
  2 | 1 | Perera | MATH     | L3        |      30
  2 | 2 | Perera | ENGL     | L1        |      10
  2 | 3 | Perera | CHIN     | L2        |      50
(6 rows)

The # column is not necessary if you want to get the result sorted by subcode or sublevel.

You should consider normalization of the model by splitting the data into two tables, e.g.:

create table students (
    id int primary key, 
    name text);

create table hours (
    id int primary key, 
    student_id int references students(id),
    code text, 
    level text, 
    hrs int);