SQL Query to get two values from a table in same result row-CodePudding

I have a table containing an identifier which always starts AB (unique) and a key value (non-unique)

ID       Key
------------------
AB1234   10001
28376    10001
AB5678   10002
7180     10002

I need to be able to query and get single row results for each Key value. There will always only be two different ID values per key so I require the following results:

ID1      ID2      Key
----------------------------
AB1234   28376    10001
AB5678   7180     10002

I'm not even sure if this is possible or not

CodePudding user response：

There will always only be two different ID values per key

In that case, something like this might do (sample data in lines #1 - 6; query begins at line #7):

SQL> with test (id, key) as
  2    (select 'AB1234', 10001 from dual union all
  3     select '1234'  , 10001 from dual union all
  4     select 'CD5678', 10002 from dual union all
  5     select '5678'  , 10002 from dual
  6    )
  7  select min(id) id1,
  8         max(id) id2,
  9         key
 10  from test
 11  group by key;

ID1    ID2           KEY
------ ------ ----------
5678   CD5678      10002
1234   AB1234      10001

SQL>

CodePudding user response：

It can be done in the below mentioned way. If you can confirm that ID2 always remains the same as substring of ID1 and the position remains the same. Below mentioned is the code for this

Select id as ID1, substr(id,3,6) as ID2, key
from <table_name> where len(id)=6
group by 3

CodePudding user response：

Try this then

with cte as (Select id as id1, key from <table_name> where len(id)=6),
cte2 as (select id as id2, key from <table_name> where len(id)=4)
select cte.id1, cte2.id2, cte.key from cte join cte2 on cte.key=cte2.key
group by 3