Skip to Main Content
  • Questions

  • want to find a way to shift column values up or down

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sharon.

Asked: November 07, 2002 - 9:27 pm UTC

Last updated: June 22, 2010 - 7:40 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom,

to illustrate what I want to accomplish, as an example, table test has two columns, which looks like

1000 12300
1002 5690
1003 10000
1010 8765

I want to shift the vaules of the second column up so that
the table will look like
1000 5690
1002 10000
1003 8765
1010 (I don't care, or make it 12300 if possible)

shift the values down is fine too.

The reason behind this is we want to shield sensitive data in HR when refresh test/dev database. I could scramble them but they might
not mean anything. By shifting the values no one knows what the original data is at the meantime we could maintains the data fairly close to real world when doing testings. This column might be salary or bonus or review rating. If you could come up with some other solutions, keep the original data but change the orders of the values of a specific column, that would be even better. Thanks Tom for your time.

and Tom said...

When you copy the data -- use analytic functions when you query it to "shift". Here is an example that shows the original val and the shifted val:

ops$tkyte@ORA920.US.ORACLE.COM> select empno, val,
2 nvl( lead(val) over (order by empno),
3 first_value(val) over ( order by empno)) shifted
4 from t
5 order by empno;

EMPNO VAL SHIFTED
---------- ---------- ----------
14160 838 646
36137 646 682
39200 682 468
39911 468 430
84512 430 838

analytic functions *rock*


Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It really is *rock* :;-))

A reader, November 08, 2002 - 8:46 am UTC

I fixed a problem for which I was writing a complex query. Thanks Tom.

The Question in words

Robert, November 08, 2002 - 10:19 am UTC

ops$tkyte@ORA920.US.ORACLE.COM> select empno, val,
2 nvl( lead(val) over (order by empno),
3 first_value(val) over ( order by empno)) shifted
4 from t
5 order by empno;

Tom, other than this "shifting" purpose, can you please put in words the question this query answers ?

Thanks


Tom Kyte
November 08, 2002 - 10:53 am UTC

well, it says:

for each row in T select the EMPNO column, the VAL column and the value of the VAL column for the next row in T after sorting by EMPNO (reach forward a row and get me their VAL as well). If that next VAL is null -- then assign the FIRST_VALUE of VAL found in T after sorting by EMPNO



This is really helpful

Sharon, November 08, 2002 - 11:20 am UTC

Tom, as always,your answers are quick, useful and fairly easy to implement. I appreciate your time and sure I'll use this approach to scramble my data, ordering it in empid or ssn order and shift the column values.

Best in Oracle - Many Thanks Tom

Marc, November 08, 2002 - 2:12 pm UTC


Oracle in Oracle : TOM

Riaz, November 11, 2002 - 4:18 am UTC


Scramble

Marko, June 16, 2005 - 2:54 am UTC

Maybe idea how to :

- shift ALL column values with offset > 1 and not
have nulls or some other default.

- not shift but randomly scrambeled values with one select
statement

Thanks



data masking

DATTA KUMAR, September 09, 2008 - 12:20 am UTC

In this example, you have addressed only the numeric field how to go about with char,varchar and date formats? I appreciate if you can help me with this.
Tom Kyte
September 09, 2008 - 7:41 am UTC

http://www.oracle.com/corporate/press/2007_nov/oem-datamasking-ow.html

would be one approach, if you "do it yourself", you'll have to sort of come up with your own algorithm - what would you like to have happen to the data.

many people makes "dictionaries" for the strings - create a table of first names from the existing data, a table of last names - then replace first and last names in the data you copy over with the first and last names randomly selected from the two tables you just made - for example.

shifting column values

jv, June 08, 2010 - 1:17 pm UTC

Dear Tom,

we have a requirement as below.

We will be receiving address data in flat file from a system and this is to be loaded into our system. The problem is there might be few fields of address that are blank and we have to move them to the end, e.g below


Sample Data:

addline1,addline2,addline3,addline4,addline5,addline6,addline7


In the above sample since all the seven fields have values no issues but if the data is something like below

addline1,null,null,null,addline5,addline6,addline7

We have to make it look like

addline1,addline5,addline6,addline7,null,null,null

any of the field can have null value,if the value is null we have to shift the not null values.

can we do this using query? if you could you please help on this.

I know this sounds absurd but we don't have control on the system sending this data and if we do this via pl/sql it will be taking a lot of time.

Thanks a lot for your help and time on this.

Regards,
jv



Tom Kyte
June 10, 2010 - 10:21 am UTC

can it also look like

addr1,,addr3,,addr5,,addr7

or are the missing values always just ONCE and contiguous as your example implies?

thanks for your time on this

JV, June 10, 2010 - 1:05 pm UTC

Dear Tom,

thanks a lot for your time on this, as you have mentioned it can look like below

addr1,,addr3,,addr5,,addr7

the missing values can be for any field.

Regards,
JV
Tom Kyte
June 11, 2010 - 6:47 am UTC

I would map the file as an external table as one line, so you can select the entire line (note: external table - do not load the data into a table, use an external table)

ops$tkyte%ORA10GR2> select x from t;

X
----------------------------------------
1,2,3,4,5,6,7
1,,3,4,5,6,7
1,,3,,5,6,7
1,,,,5,6,7
1,,,4,,,7
1,,,,,,7
1,,,,,,
,,,4,,,
,,,,,,



so, there is our address data, we can then:

ops$tkyte%ORA10GR2> select x, ','||trim( ',' from replace(replace(x,',,,',','),',,',',') )||','y from t;

X                                        Y
---------------------------------------- --------------------
1,2,3,4,5,6,7                            ,1,2,3,4,5,6,7,
1,,3,4,5,6,7                             ,1,3,4,5,6,7,
1,,3,,5,6,7                              ,1,3,5,6,7,
1,,,,5,6,7                               ,1,5,6,7,
1,,,4,,,7                                ,1,4,7,
1,,,,,,7                                 ,1,7,
1,,,,,,                                  ,1,
,,,4,,,                                  ,4,
,,,,,,                                   ,,

9 rows selected.



and now we can:

ops$tkyte%ORA10GR2> select x,
  2      trim( substr (txt, instr (txt, ',', 1, 1  ) + 1, instr (txt, ',', 1, 1+1) - instr (txt, ',', 1, 1) -1 ) ) a1,
  3      trim( substr (txt, instr (txt, ',', 1, 2  ) + 1, instr (txt, ',', 1, 2+1) - instr (txt, ',', 1, 2) -1 ) ) a2,
  4      trim( substr (txt, instr (txt, ',', 1, 3  ) + 1, instr (txt, ',', 1, 3+1) - instr (txt, ',', 1, 3) -1 ) ) a3,
  5      trim( substr (txt, instr (txt, ',', 1, 4  ) + 1, instr (txt, ',', 1, 4+1) - instr (txt, ',', 1, 4) -1 ) ) a4,
  6      trim( substr (txt, instr (txt, ',', 1, 5  ) + 1, instr (txt, ',', 1, 5+1) - instr (txt, ',', 1, 5) -1 ) ) a5,
  7      trim( substr (txt, instr (txt, ',', 1, 6  ) + 1, instr (txt, ',', 1, 6+1) - instr (txt, ',', 1, 6) -1 ) ) a6,
  8      trim( substr (txt, instr (txt, ',', 1, 7  ) + 1, instr (txt, ',', 1, 7+1) - instr (txt, ',', 1, 7) -1 ) ) a7
  9    from
 10  ( select x, ','||trim( ',' from replace(replace(x,',,,',','),',,',',') )||',' txt from t );

X                    A1    A2    A3    A4    A5    A6    A7
-------------------- ----- ----- ----- ----- ----- ----- -----
1,2,3,4,5,6,7        1     2     3     4     5     6     7
1,,3,4,5,6,7         1     3     4     5     6     7
1,,3,,5,6,7          1     3     5     6     7
1,,,,5,6,7           1     5     6     7
1,,,4,,,7            1     4     7
1,,,,,,7             1     7
1,,,,,,              1
,,,4,,,              4
,,,,,,

9 rows selected.


that is what you load, you load from an external table and using sql get what you need.

a liitle bit better "human readable"

Sokrates, June 11, 2010 - 9:01 am UTC

, but equivalent to
select x,
  trim( substr (txt, instr (txt, ',', 1, 1  ) + 1, instr (txt, ',', 1, 1+1) - instr (txt,
',', 1, 1) -1 ) ) a1,
  trim( substr (txt, instr (txt, ',', 1, 2  ) + 1, instr (txt, ',', 1, 2+1) - instr (txt,
',', 1, 2) -1 ) ) a2,
  trim( substr (txt, instr (txt, ',', 1, 3  ) + 1, instr (txt, ',', 1, 3+1) - instr (txt,
',', 1, 3) -1 ) ) a3,
  trim( substr (txt, instr (txt, ',', 1, 4  ) + 1, instr (txt, ',', 1, 4+1) - instr (txt,
',', 1, 4) -1 ) ) a4,
  trim( substr (txt, instr (txt, ',', 1, 5  ) + 1, instr (txt, ',', 1, 5+1) - instr (txt,
',', 1, 5) -1 ) ) a5,
  trim( substr (txt, instr (txt, ',', 1, 6  ) + 1, instr (txt, ',', 1, 6+1) - instr (txt,
',', 1, 6) -1 ) ) a6,
  trim( substr (txt, instr (txt, ',', 1, 7  ) + 1, instr (txt, ',', 1, 7+1) - instr (txt,
',', 1, 7) -1 ) ) a7
  from
 ( select x, ','||trim( ',' from replace(replace(x,',,,',','),',,',',') )||',' txt from t )


would probably be

select 
   x, 
   substr(r,1,1) a1,
   substr(r,2,1) a2,
   substr(r,3,1) a3,
   substr(r,4,1) a4,
   substr(r,5,1) a5,
   substr(r,6,1) a6,
   substr(r,7,1) a7
from
(select x, replace(x, ',') r from t)


?
Tom Kyte
June 22, 2010 - 7:39 am UTC

you are making the assumption each string is 1 character long. It is a bunch of addresses, you don't know their length.

got it

Sokrates, June 11, 2010 - 9:05 am UTC

ok, got it
in case you have data with length > 1 your sql is better

by the way: up africa !
(watching tv *and* thinking in sql can be quite difficult)
Tom Kyte
June 22, 2010 - 7:40 am UTC

:)

iT wORKS!

Hillol Sarker, August 14, 2019 - 11:56 am UTC

I was writing a very complex query to shift the column values, but this seems to be the masterpiece!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.