How to pivot some of data fields in postgres

Question

I tried using max(case) but it didn't work to convert the unpivot data to pivot format.

Can we pivot this data in the following format?

My table:

Create table mytable (id integer,indicator text,value text);

Insert into mytable (id, indicator, value) values (1,'indicator 1', '10');
Insert into mytable (id, indicator, value) values (1,'indicator 2', 'yes');
Insert into mytable (id, indicator, value) values (1,'indicator 3', '123');
Insert into mytable (id, indicator, value) values (1,'state', 'ns');
Insert into mytable (id, indicator, value) values (1,'district', 'yr');
Insert into mytable (id, indicator, value) values (1,'block', 'ty');

id	indicator	value
1	indicator 1	10
1	indicator 2	yes
1	indicator 3	123
1	state	ns
1	district	yr
1	block	ty

Output should be like below:

id	state	district	block	indicator	value
1	ns	yr	ty	indicator 1	10
1	ns	yr	ty	indicator 2	yes
1	ns	yr	ty	indicator 3	123

Answer 1

You seem to want to pivot the state/district/block rows to columns, while retaining "indicator" rows.

One option uses window function to pivot, then filters:

select id, state, district, block, indicator, value
from (
    select t.*,
        max(value) filter(where indicator = 'state')    over(partition by id) as state,
        max(value) filter(where indicator = 'district') over(partition by id) as district,
        max(value) filter(where indicator = 'block')    over(partition by id) as block
    from mytable t
) t
where indicator like 'indicator%'

id	state	district	block	indicator	value
1	ns	yr	ty	indicator 1	10
1	ns	yr	ty	indicator 2	yes
1	ns	yr	ty	indicator 3	123

fiddle