Alter SQL table

55 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

<pre>
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
</pre>

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.

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    You will probably need to create a function to achieve that. What database are you using ?
    68,780 pointsBadges:
    report
  • Lazy167
    how would i go abouts doing that? i've never created a function before i'm using oracle,
    55 pointsBadges:
    report
  • Lazy167
    well i have created some basic sum and count function but nothing like this
    55 pointsBadges:
    report
  • Lazy167
    so does anyone have any idea how i should go abouts doing this?
    55 pointsBadges:
    report
  • carlosdl
    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.
    68,780 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • carlosdl
    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; -----------------------
    68,780 pointsBadges:
    report
  • carlosdl
    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.
    68,780 pointsBadges:
    report
  • msi77
    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
    1,660 pointsBadges:
    report
  • carlosdl
    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;
    68,780 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following