Mess up data in tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Mess up data in tables

Author

Topic

mangons
Starting Member

2 Posts

Posted - 2006-02-16 : 07:50:20

Hi,

Sorry if someone already asked for this, didn´t find it:
Here is what I need

I have some tables with different columns

A B C D
--------------------------------------------------
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4

I need a script to run against that table to get something like:

A B C D
--------------------------------------------------
A4 B1 C4 D2
A3 B2 C2 D1
A2 B3 C1 D4
A1 B4 C3 D3

or

A B C D
--------------------------------------------------
A2 B4 C1 D3
A3 B1 C3 D2
A1 B2 C2 D4
A4 B3 C4 D1

So the idea is to get the table with the same data but completely messed up

Thanks!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 08:45:17

This should do it. Don't think it is efficient but maybe somebody else can help to improve this.

If you need to randomly return 4 rows, use order by newid()

create table #test
(
	A	varchar(10),
	B	varchar(10),
	C	varchar(10),
	D	varchar(10)
)

insert into #test
select 'A1' as [A], 'B1' as [B], 'C1' as [C], 'D1' as [D]	union all
select 'A2' as [A], 'B2' as [B], 'C2' as [C], 'D2' as [D]	union all
select 'A3' as [A], 'B3' as [B], 'C3' as [C], 'D3' as [D]	union all
select 'A4' as [A], 'B4' as [B], 'C4' as [C], 'D4' as [D]

select	A, B, C, D
from
(
	select 	top 100 percent A, (select count(*) from #test x where x.A <= a.A) as row_no
	from 	#test a
	order by A
) as aa
cross join
(
	select 	top 100 percent B, (select count(*) from #test x where x.B <= b.B) as row_no
	from 	#test b
	order by B
) as bb
cross join
(
	select 	top 100 percent C, (select count(*) from #test x where x.C <= c.C) as row_no
	from 	#test c
	order by C
) as cc
cross join
(
	select 	top 100 percent D, (select count(*) from #test x where x.D <= d.D) as row_no
	from 	#test d
	order by D
) as dd
where	aa.row_no <> bb.row_no
and	aa.row_no <> cc.row_no
and	aa.row_no <> dd.row_no
and	bb.row_no <> cc.row_no
and	bb.row_no <> dd.row_no
and	cc.row_no <> dd.row_no	
order by A, B, C, D

----------------------------------
'KH'
Time is always against us

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 08:46:11

By the way . . what do you want to do this ?

----------------------------------
'KH'
Time is always against us

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-16 : 09:03:51

Tan, for large number of data, that approach will be ineffecient

mangons, where do you want to show these jumbled data?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 09:06:58

quote:
Originally posted by madhivanan

Tan, for large number of data, that approach will be ineffecient

mangons, where do you want to show these jumbled data?

Madhivanan

Failing to plan is Planning to fail

Yes. I agree. The requiement for doing this is also a bit unusual

----------------------------------
'KH'
Time is always against us

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 09:31:08

use INNER JOIN with ON not equal

create table #test
(
	A	varchar(10),
	B	varchar(10),
	C	varchar(10),
	D	varchar(10)
)

insert into #test
select 'A1' as [A], 'B1' as [B], 'C1' as [C], 'D1' as [D]	union all
select 'A2' as [A], 'B2' as [B], 'C2' as [C], 'D2' as [D]	union all
select 'A3' as [A], 'B3' as [B], 'C3' as [C], 'D3' as [D]	union all
select 'A4' as [A], 'B4' as [B], 'C4' as [C], 'D4' as [D]

select	A, B, C, D
from
(
	select 	top 100 percent A, (select count(*) from #test x where x.A <= a.A) as row_no
	from 	#test a
	order by A
) as aa
inner join
(
	select 	top 100 percent B, (select count(*) from #test x where x.B <= b.B) as row_no
	from 	#test b
	order by B
) as bb
on	aa.row_no	<> bb.row_no
inner join
(
	select 	top 100 percent C, (select count(*) from #test x where x.C <= c.C) as row_no
	from 	#test c
	order by C
) as cc
on	aa.row_no	<> cc.row_no
and	bb.row_no	<> cc.row_no
inner join
(
	select 	top 100 percent D, (select count(*) from #test x where x.D <= d.D) as row_no
	from 	#test d
	order by D
) as dd
on	aa.row_no	<> dd.row_no
and	bb.row_no	<> dd.row_no
and	cc.row_no	<> dd.row_no
order by A, B, C, D

----------------------------------
'KH'
Time is always against us

mangons
Starting Member

2 Posts

Posted - 2006-02-16 : 09:37:32

Hi there,

Thanks for your help...

Obviously my tables are different for those in the example, different data types, lenghts, etc... an also, quite larger (about 50000 rows on average)

Apart from that and to make it more difficult, I need to mess just some columns, lets say only column B and D

And...why do I need to do it?

It is just a matter of privacy, can´t give that DB to anybody unless I mess it up...And I need someone to work with it to develop an application

The idea is to get a script which I can run as many times as I need on my DB (few tables)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources