Sql concatenate and drop in two separate columns
I have an ordered table
form_id | procedure_id
----------+-------------
101 | 24
101 | 23
101 | 22
102 | 7
102 | 6
102 | 3
102 | 2
And in another table the table is executed
form_id | procedure_id
----------+-------------
101 | 42
101 | 45
102 | 5
102 | 3
102 | 7
102 | 12
102 | 13
Expected Result
form_id o_procedure_id p_procedure_id
101 24 42
101 23 45
101 22 NULL
102 7 7
102 6 5
102 3 3
102 2 12
102 NULL 13
I tried the following query:
with ranked as
(select
dense_rank() over (partition by po.form_id order by po.procedure_id) rn1,
dense_rank() over (partition by po.form_id order by pp.procedure_id) rn2,
po.form_id,
po.procedure_id,
pp.procedure_id
from ordered po,
performed pp where po.form_id = pp.form_id)
select ranked.* from ranked
--where rn1=1 or rn2=1
The above query returns a value with the order number and procedure ID. How to get Excluded Withdrawal?
+3
source to share
1 answer
I was not sure how you want to handle a lot of null values and / or null values on either side of your tables. My example for this assumes that the first table will be leading and includes all records, and the second table can contain holes. The request is not pretty, but I suppose it does what you expect it to do:
select test1_sub.form_id, test1_sub.process_id as pid_1, test2_sub.process_id as pid_2 from (
select form_id,
process_id,
rank() over (partition by form_id order by process_id asc nulls last)
from test1) as test1_sub
left join (
select * from (
select form_id,
process_id,
rank() over (partition by form_id order by process_id asc nulls last)
from test2
) as test2_nonexposed
) as test2_sub on test1_sub.form_id = test2_sub.form_id and test1_sub.rank = test2_sub.rank;
+1
source to share