CODEWITHSUNDEEP
postgresql
harsh atal
harsh atal

Reputation: 409

How can i append multiple columns of type integer into a single column

Following is my query :-

select 
ou.organisationunitid as one,
ou2.organisationunitid as two,
ou3.organisationunitid as three,
ou4.organisationunitid as four,
ou5.organisationunitid as five,
ou6.organisationunitid as six,
ou7.organisationunitid as seven,
ou8.organisationunitid as eight,
ou9.organisationunitid as nine,
ou10.organisationunitid as ten ,
ou11.organisationunitid as eleven
from orgunitgroupmembers ougm
.
.
.
group by   ou.organisationunitid,ou2.organisationunitid,ou3.organisationunitid,ou4.organisationunitid,ou5.organisationunitid,ou6.organisationunitid,ou7.organisationunitid,ou8.organisationunitid,ou9.organisationunitid,ou10.organisationunitid,ou11.organisationunitid

This is the output : enter image description here

I want to combine all these IDs of type integer into a single column so that I can feed it into another query. For eg: select * from orgunit where id in ( Output from above query )

I tried to string concat but that will not work as I need to feed it into another query.

So, is there a way through which this can be achieved? Any help will be deeply appreciated. Please suggest a solution which is compatible with feeding the resulting single column output as input into another query.

Note: I am working in a constrained environment so any "functions" or plugins I cannot use.

Thanks

OK, major edit:

Here is the fiddle -> https://www.db-fiddle.com/f/tKYF4jeDm8B5cWLdhWav3o/0

What I am trying to do is that I want to get the orgunitgroupmembers as well as all their children in the hierarchy. The relation between self joins is that of parent-child.

Also pasting here the create tables and query :

     CREATE TABLE organisationunit (
  organisationunitid integer,
  parentid integer

);

INSERT INTO organisationunit (organisationunitid,parentid) VALUES (1,NULL);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (2,1);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (3,2);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (4,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (5,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (6,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (7,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (8,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (9,3);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (10,4);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (11,4);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (12,4);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (13,5);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (14,5);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (15,5);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (16,5);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (17,6);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (18,6);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (19,6);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (20,7);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (21,7);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (22,7);
INSERT INTO organisationunit (organisationunitid,parentid) VALUES (23,7);


CREATE TABLE orgunitgroupmember (
  orgunitgroupid integer,
  organisationunitid integer  
);

INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,10);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,11);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,5);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,6);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,8);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,22);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (1,3);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (2,15);
INSERT INTO orgunitgroupmember (orgunitgroupid,organisationunitid) VALUES (3,22);

CREATE TABLE orgunitgroup (
  orgunitgroupid integer
);

INSERT INTO orgunitgroup (orgunitgroupid) VALUES (1);

Full Query :

select 
ou.organisationunitid as one,
ou2.organisationunitid as two,
ou3.organisationunitid as three,
ou4.organisationunitid as four,
ou5.organisationunitid as five,
ou6.organisationunitid as six,
ou7.organisationunitid as seven,
ou8.organisationunitid as eight,
ou9.organisationunitid as nine,
ou10.organisationunitid as ten ,
ou11.organisationunitid as eleven
from orgunitgroupmember ougm
inner join organisationunit ou on ou.organisationunitid =  ougm.organisationunitid
left join organisationunit ou2 on ou.organisationunitid =  ou2.parentid
left join organisationunit ou3 on ou2.organisationunitid =  ou3.parentid
left join organisationunit ou4 on ou3.organisationunitid =  ou4.parentid
left join organisationunit ou5 on ou4.organisationunitid =  ou5.parentid
left join organisationunit ou6 on ou5.organisationunitid =  ou6.parentid
left join organisationunit ou7 on ou5.organisationunitid =  ou7.parentid
left join organisationunit ou8 on ou5.organisationunitid =  ou8.parentid
left join organisationunit ou9 on ou5.organisationunitid =  ou9.parentid
left join organisationunit ou10 on ou5.organisationunitid =  ou10.parentid
left join organisationunit ou11 on ou5.organisationunitid =  ou11.parentid

inner join orgunitgroup oug on oug.orgunitgroupid = ougm.orgunitgroupid
where oug.orgunitgroupid in (1)
group by ou.organisationunitid,ou2.organisationunitid,ou3.organisationunitid,ou4.organisationunitid,ou5.organisationunitid,ou6.organisationunitid,ou7.organisationunitid,ou8.organisationunitid,ou9.organisationunitid,ou10.organisationunitid,ou11.organisationunitid

Upvotes: 1

Views: 54

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51649

you can try a lazy hack, e.g. you have:

t=# with c(x,y,z,w) as (values(1,null,3,4),(5,6,7,null))
select *,translate(regexp_replace(c::text,',([^0-9])',',null\1','g'),'()','{}')::int[] from c;
 x | y | z | w |  translate
---+---+---+---+--------------
 1 |   | 3 | 4 | {1,NULL,3,4}
 5 | 6 | 7 |   | {5,6,7,NULL}
(2 rows)

so just aggregate them:

t=# with c(x,y,z,w) as (values(1,null,3,4),(5,6,7,null))
,un as (select unnest(translate(regexp_replace(c::text,',([^0-9])',',null\1','g'),'()','{}')::int[]) from c)
select array_agg(unnest) from un;
        array_agg
-------------------------
 {1,NULL,3,4,5,6,7,NULL}
(1 row)

lastly use any:

t=# with c(x,y,z,w) as (values(1,null,3,4),(5,6,7,null))
,un as (select unnest(translate(regexp_replace(c::text,',([^0-9])',',null\1','g'),'()','{}')::int[]) from c)
, ag as (select array_agg(unnest) from un)
select 'there' from ag where 3 = any(array_agg);
 ?column?
----------
 there
(1 row)

t=# with c(x,y,z,w) as (values(1,null,3,4),(5,6,7,null))
,un as (select unnest(translate(regexp_replace(c::text,',([^0-9])',',null\1','g'),'()','{}')::int[]) from c)
, ag as (select array_agg(unnest) from un)
select 'there' from ag where 2 = any(array_agg);
 ?column?
----------
(0 rows)

of course "casting" tuple to array can't be reliable and used on prod. you would need to name all fields probably. But such monkey hack can save your typing for simple check

Upvotes: 1

user330315
user330315

Reputation:

That looks like you actually need a recursive query that can be used as a sub-query:

with recursive org_units as (
  select ou.organisationunitid, 1 as level
  from orgunitgroupmember ougm
    join organisationunit ou on ou.organisationunitid =  ougm.organisationunitid
    join orgunitgroup oug on oug.orgunitgroupid = ougm.orgunitgroupid
  where oug.orgunitgroupid in (1)

  union all

  select ch.organisationunitid, p.level + 1
  from organisationunit ch 
    join org_units p on ch.parentid = p.organisationunitid
)
select ... 
from ..... -- << here is your main query 
where some_id in (select organisationunitid from org_units);
                  ^
                  | this is where you re-use the result of the recursive query

This also has the advantage that you don't need to know how many levels your hierarchy has.

Upvotes: 0

Related Questions