You will probably need to create a function to achieve that.

What database are you using ?

how would i go abouts doing that? i’ve never created a function before
i’m using oracle,

well i have created some basic sum and count function but nothing like this

so does anyone have any idea how i should go abouts doing this?

One option could be using a function like this:

CREATE OR REPLACE FUNCTION codes_of (p_Number in number) RETURN varchar2 IS
	l_Codes varchar2(20);
BEGIN
  FOR i IN (SELECT code FROM your_table WHERE numbers = p_number) LOOP
  	l_Codes := l_Codes || i.code || ',';
  END LOOP;
  RETURN SUBSTR(l_Codes,1,length(l_Codes)-1);
END;

Then you could use a query like this:

SELECT DISTINCT numbers,codes_of(numbers)
FROM your_table;

Notice that this will return all codes (no matter how many codes a number can have) in a single column, separated by commas.

Please let us know if this works for you, so we can put it as an answer. If that’s not what you need, let us know.

There is also the brute force way. Note – this will work for relatively small tables – if you have a large table you may have trouble with rollback segments.

1.
Alter table MyTable add Code2 varchar(something)

2.
Update MyTable MT2
set Code2 = (select Code1 from MyTable MT1
where MT1.Number = MT2.Number and MT1.Code1 > MT2.Code1)

3.
Delete from MyTable MT2
where exists (select 1 from MyTable MT1
where MT1.Number = MT2.Number and MT1.Code1 > MT2.Code1)

This isn’t pretty, but should achieve what you asked. Obviously, I would back up the table before trying this.

Another way:

———————–

create table your_table_2 as
SELECT t1.numbers,t1.c code_1,DECODE(t2.c,t1.c,null,t2.c) code_2
FROM
(SELECT numbers,min(code) c FROM your_table GROUP BY numbers) t1,
(SELECT numbers,max(code) c FROM your_table GROUP BY numbers) t2
WHERE t1.numbers = t2.numbers;

drop table your_table;

rename your_table_2 to your_table;

———————–

But,

Do you really want to change the table structure ?

Depending on the data and business rules, your current structure could be a better design than the one you want to change to. If you add a column to identify the code type, your design could support many different codes for each number without changes, but under the new design, you would need to alter the table structure (and applications) each time a new code is needed.

Somewhat ansi solution:

select number, max(case when rnk=1 then code end) code1, max(case when rnk=2 then code end) code2
from (
select *, RANK() over(partition by number order by code) rnk
from yourtable
) x
group by number

For the above code to work in Oracle, you need to qualify the ‘*’ in the subquery.

Something like this:

select number, max(case when rnk=1 then code end) code1, max(case when rnk=2 then code end) code2
from (
select y.*, RANK() over(partition by number order by code) rnk
from yourtable y
) x
group by number;