How to merge multiple rows in one column with hierarchy Postgresql 12?

Question

I have a table like this to save the catalog of 90 services:

-------------------------------------------------------------------------------------
|  id  |  service     | subservice      | description                      | cost
-------------------------------------------------------------------------------------
| 2044 |   Tests      |   Tests         | Calcium                          | 50.00
| 1385 |   Cardiology |   Cardioversion | Electric Cardioversion programmed| 200.00
| 7000 |   Cardiology |  Ecocardiography| Chest Ultrasound                 | 100.00
-------------------------------------------------------------------------------------

I need to change the table structure in order to have the three levels (service, subservice and description) in the same column with its own id and pointing to a new column with the level's number. Namely something like this (note that the id's are something that I made up):

-------------------------------------------------------------------------------------
|  id  | description  | parent_id   |  cost
-------------------------------------------------------------------------------------
| 1    |   Tests      |       NULL  | 0.00
| 2    |   Tests      |         1   | 0.00
| 2044 |   Calcium    |         2   | 50.00
-------------------------------------------------------------------------------------

I'm working on Postgresql 12. I have created the new column parent_id and I was trying to do this sequence:

CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

ALTER TABLE catalog
ALTER COLUMN parent_id SET DEFAULT nextval('seq_parent_id')

Can anyone please give a rough idea how to transform the structure of the table?

First .... You can not use sequence on parent_id table. Sencond do you want to store the existing data into new transformed table? — Akhilesh Mishra
– Akhilesh Mishra, Commented May 29, 2021 at 8:39
Yes, I want to store the existing data into the new transform table. — tucomax
– tucomax, Commented May 29, 2021 at 8:41

Akhilesh Mishra · Accepted Answer · 2021-05-29 09:34:53Z

1

Its a bit lengthy, So the steps are:

Old Table

create table test (id int, service varchar, subservice varchar, description varchar, cost decimal(10,2));

New Table

create table newtest(id int, description varchar, parent_id int, cost decimal(10,2));

Create a sequence as you have mentioned in your question

CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

Now Run below query

with cte(id, description, parent_id,cost) as 
(select nextval('seq_parent_id'), service, null::bigint,0::decimal  
from test group by 2
),
cte1(id, description, parent_id,cost) as 
(select nextval('seq_parent_id'), subservice, t2.id,0::decimal 
from test t1 
inner join cte t2 on t1.service=t2.description and t2.parent_id is null group by 2,3
)

insert into newtest

select * from cte
union all
select * from cte1
union all
select t1.id,t1.description,t3.id,t1.cost from test t1 
inner join cte t2 on t1.service=t2.description and t2.parent_id is null
inner join cte1 t3 on t1.subservice=t3.description and t3.parent_id=t2.id

DEMO

Explanation of above query is demonstrated in this fiddle

NOTE: Before running the query please ensure that counter value will not cross the already existing ID. If newly inserted rows are more than minimum existing count then set the start of sequence max(id)+1 before running this query.

edited May 29, 2021 at 9:34

answered May 29, 2021 at 9:27

Akhilesh Mishra

1808 bronze badges

This is great, thank you. I was stuck there, I did not know how to do it with CTE.

tucomax
– tucomax

2021-05-29 11:55:54 +00:00
Commented May 29, 2021 at 11:55
One thing, is it possible to do this without creating a new table? Thanks.

tucomax
– tucomax

2021-05-29 14:19:45 +00:00
Commented May 29, 2021 at 14:19
then where you will store new data, in same table?

Akhilesh Mishra
– Akhilesh Mishra

2021-05-29 14:20:18 +00:00
Commented May 29, 2021 at 14:20
I was thinking in the same table test and dropping service and subservice columns.

tucomax
– tucomax

2021-05-29 14:22:07 +00:00
Commented May 29, 2021 at 14:22
1

Actually it will be all together a different question. This is the fiddle for your additional question.

Akhilesh Mishra
– Akhilesh Mishra

2021-05-29 14:35:07 +00:00
Commented May 29, 2021 at 14:35

| Show 1 more comment

Stack Exchange Network

How to merge multiple rows in one column with hierarchy Postgresql 12?

1 Answer 1

Your Answer

Hot Network Questions

How to merge multiple rows in one column with hierarchy Postgresql 12?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions