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

klin · Accepted Answer · 2017-02-16 09:33:01Z

3

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);

edited Feb 16, 2017 at 9:33

answered Feb 15, 2017 at 12:12

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chandana Indisooriya Over a year ago

Many thanks for the help given. This is working fine with using "union all". And thanks for table normalization tip as well. Unfortunately we can not change table structure now.

klin Over a year ago

Yes, union all makes sense, I've added this for a future reader.

Collectives™ on Stack Overflow

Postgresql Split single row to multiple rows

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related