Alter SQL table
Home > IT Answers > Development > Alter SQL table
Lazy167
55 pts.
0
Q:
Alter SQL table
SQL, SQL tables
hi i have a set of data

number | code
1 | abc
2 |ff
3 |aa
3 |bb
4 |cc
5 |dd
5 |ee

and i'm wondering if there is a way to convert this data so it looks like

number | code1 |code2
1 | abc |null
2 |ff |null
3 |aa |bb
4 |cc |null
5 |dd |ee

thx
ASKED: May 28 2009  11:21 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Pr09
70 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button

 USE yourdb
 GO
 
 ALTER TABLE dbo.yourtable ADD code1 VARCHAR(20) NULL
 ALTER TABLE dbo.yourtable ADD code2 VARCHAR(20) NULL
 GO
 
 DECLARE @number int
 DECLARE @code varchar(20)
 DECLARE @code1 varchar(20)
 DECLARE @code2 varchar(20)
 
 SELECT * INTO #yertbl FROM dbo.yourtable
 
 DECLARE tblcur CURSOR FOR SELECT number, code FROM #yertbl ORDER BY number
 
 OPEN tblcur
 
 FETCH NEXT FROM tblcur INTO @number, @code
 
 DECLARE @lastnum int
 SET @lastnum = @number - 1
 
 WHILE @@FETCH_STATUS = 0
 	BEGIN
 	IF @lastnum = @number
 		BEGIN
 		UPDATE dbo.yourtable SET code2 = @code WHERE number = @number
 		DELETE dbo.yourtable WHERE number = @number AND code = @code
 		END
 	ELSE
 		UPDATE dbo.yourtable SET code1 = @code WHERE number = @number
 
 	SET @lastnum = @number
 
 	FETCH NEXT FROM tblcur INTO @number, @code
 	END
 
 CLOSE tblcur
 DEALLOCATE tblcur
 GO
 
 DROP TABLE #yertbl
 ALTER TABLE dbo.yourtable DROP COLUMN code
 GO


I wrote this in T-SQL (not sure of what chages for oracle dbs). It starts by adding 2 new columns to your database named (code1 and code2). It then moves your table into a temporary table and iterates through it one record at a time updating the table by filling in code1/code2 with their respective values, pulled from the temp table. It then drops the temp table and drops the old 'code' column in your original table, leaving you with your desired format.

Note: (all you have to do is change "dbo.yourtable" to the name of your table and "yourdb" to the name of your database) AND PLEASE backup your data before you try running this script :) Hope it helps.
Last Answered: May 29 2009  3:33 PM GMT by Pr09   70 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29820 pts.  |   May 28 2009  2:35PM GMT

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

What database are you using ?

 

Lazy167   55 pts.  |   May 28 2009  9:35PM GMT

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

 

Lazy167   55 pts.  |   May 28 2009  11:40PM GMT

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

 

Lazy167   55 pts.  |   May 29 2009  7:45AM GMT

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

 

Carlosdl   29820 pts.  |   May 29 2009  1:56PM GMT

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.

 

Kccrosser   1850 pts.  |   May 29 2009  6:46PM GMT

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.

 

Carlosdl   29820 pts.  |   May 29 2009  8:16PM GMT

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;

———————–

 

Carlosdl   29820 pts.  |   May 29 2009  8:17PM GMT

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.

 

Msi77   800 pts.  |   May 29 2009  10:05PM GMT

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

 

Carlosdl   29820 pts.  |   May 29 2009  10:39PM GMT

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;

 
0