delete duplicate columns - 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 2005 Forums

Transact-SQL (2005)

delete duplicate columns

Author

Topic

gagan.707
Starting Member

9 Posts

Posted - 2007-10-31 : 03:15:21

i have a table with 10k+ rows and some duplicate columns.
i need to find the duplicate columns ande remove them.
comparing row by row isnt an esfficient solution.
better solution required...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 03:25:20

What do you mean by duplicate column ? Please post some sample data to illustrate this and how do you want to remove it.

KH
[spoiler]Time is always against us[/spoiler]

gagan.707
Starting Member

9 Posts

Posted - 2007-10-31 : 03:34:46

both columns having same data but diff col name.
[col1] [col2] [col3]
4926 4926 85503
4771 4771 85503
4771 4771 85503
4898 4898 85503
4897 4897 85503
4897 4897 85503

i will copy records, taking duplicate columns just once, in a table varaible and use it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 03:36:19

use the distinct ?

select distinct col1, col2, col
from   yourtable

KH
[spoiler]Time is always against us[/spoiler]

gagan.707
Starting Member

9 Posts

Posted - 2007-10-31 : 03:40:20

doesnt work ..
i need only col1 and col3

gagan.707
Starting Member

9 Posts

Posted - 2007-10-31 : 03:41:56

i need to first check which columns are duplicate and then copy just one of them in a temp varaible.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 03:44:51

quote:
Originally posted by gagan.707

both columns having same data but diff col name.
[col1] [col2] [col3]
4926 4926 85503
4771 4771 85503
4771 4771 85503
4898 4898 85503
4897 4897 85503
4897 4897 85503

i will copy records, taking duplicate columns just once, in a table varaible and use it.

which are the records that you want ? ?

KH
[spoiler]Time is always against us[/spoiler]

gagan.707
Starting Member

9 Posts

Posted - 2007-10-31 : 03:48:20

first i need to check whether all rows of any of the columns are same.
here all rows of col1 and col2 are same..
so i need only one of them...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 03:50:55

[code]select col1, col2, min(col3)
from yourtable
group by col1, col2[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 04:23:31

quote:
Originally posted by gagan.707

first i need to check whether all rows of any of the columns are same.
here all rows of col1 and col2 are same..
so i need only one of them...

Can you post the expected outcome?

Madhivanan

Failing to plan is Planning to fail

gagan.707
Starting Member

9 Posts

Posted - 2007-10-31 : 04:43:51

[col1] [col3]
4926 85503
4771 85503
4771 85503
4898 85503
4897 85503
4897 85503

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 04:47:21

Well. What do you want to have if the sample data are following?

4926 4926 85503
4771 4771 85503
4771 4776 85503
4898 4898 85503
4897 4897 85503
4897 4898 85503

Madhivanan

Failing to plan is Planning to fail

gagan.707
Starting Member

9 Posts

Posted - 2007-10-31 : 04:55:45

[col1] [col3]
4926 85503
4771 85503
4771 85503
4898 85503
4897 85503
4897 85503

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 05:02:43

Select col1, col3 from table

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-10-31 : 10:22:21

ooo here we go... how about.. normalize your data?

Do it in the presentation layer?

Cursors are evil?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources