Erasing some repeating fields - 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)

Erasing some repeating fields

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2007-09-25 : 19:19:04

I have these rows:



ColumnA     ColumnB     ColumnC     ColumnD
Loan1         200       House1         1000
Loan2         150       House1         1000
Loan3         150       House1         1000

ColumnB is Loan amount.

What i want is this:



ColumnA     ColumnB     ColumnC     ColumnD
Loan1         200       House1         1000
Loan2         150       House1           0
Loan3         150       House1           0

Thats because, if i let that fields, it sum 3000, and the idea here is one house of 1000, for 3 loans of 500

I wrote a cursor that group me by house, so i let the first register, and the others i replaced with zero, helping me with a flag boolean.

But when i used this for thousends of rows, the render is too slow.

Is there another way to do this?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-25 : 19:27:47

Do you have any PK column in this table? preferable a numeric column?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 01:48:02

Is the data above the result of your existing query, or the actual table layout?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 03:06:26

Does this sound Suppress if duplicated?

Madhivanan

Failing to plan is Planning to fail

rekiller
Starting Member

31 Posts

Posted - 2007-09-26 : 10:05:09

Is not a table. Is a result from multiple queries executed, from a DataWareHouse. But i catch them into a temp table. What i want is dissapear some data. It could be done? Or is better in an presentation layer?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 10:41:21

Madhi told you how to do it seven hours ago..

E 12°55'05.25"
N 56°04'39.16"

rekiller
Starting Member

31 Posts

Posted - 2007-09-26 : 10:53:44

Hmm, how do you do that? Supress if duplicated?

I know that in Crystal is easy.

But i can only use, sql server for retrieve data, and excel with vba code, to show data and make reports. In excel i can not use Ado because is not allowed, only queryTables.

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 09:43:58

If you want to get the data "presentation ready" in SQL Server, rather than using your presentation layer itself (which seems like a bad idea, but anyhow ...)

Make sure that the TEMP table has an ID/Identity column in ascending [presentation] order

Then you can UPDATE the TEMP table and set ColumnD to 0 where there is a matching row in ColumnC with a lower ID/Identity column value.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources