Changing column values in duplicate rows

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 2008 Forums

Transact-SQL (2008)

Changing column values in duplicate rows

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2013-09-07 : 16:27:22

I had originally posted this in sqlserver 2005 but this is for sql server 2008 so I am reposting it here.

I have a select query that has the following result

Code| Name| ItemID| RecDate| ProdDate| QtyRec| QtyUsed

A | AF | A-217 | 1/1/2013 | 2/3/2013 | 20| 30
A | AF | A-218 | 1/2/2013 | 3/3/2013 | 22| 24
A | AF | A-218 | 1/2/2013 | 3/3/2013 | 22| 34
A | AF | A-219 | 1/2/2013 | 3/3/2013 | 24| 50
B | FF | A-333 | 1/4/2013 | 4/4/2013 | 23| 34
B | FF | A-333 | 1/6/2013 | 5/5/2013 | 34| 78
B | FF | A-335 | 1/6/2013 | 6/6/2013 | 56| 79

These will be grouped by the Code. But the main thing is this: if the ItemID is duplicated within the code (and they will be in order), then only the first instance will have all the columns visible, all the other instances will have only the prodDate and qtyused appearing. The other columns will be blank. For example (the rows with only two values are a date in the proddate column and a value in the QtyUsed column

Code| Name| ItemID| RecDate| ProdDate| QtyRec | QtyUsed

A|
AF| A-217| 1/1/2013| 2/3/2013| 20| 30
3/3/2013| 24
3/3/2013| 34
AF| A-219| 1/2/2013| 3/3/2013| 24| 50
B |
FF| A-333| 1/4/2013| 4/4/2013| 23| 34
5/5/2013| 78
FF| A-335| 1/6/2013| 6/6/2013| 56| 79

I noticed some samples using Outer Apply but they had other things that are different from mine so I could not understand if that would be what would be used and if so how it would be used.

The way I plan to do this is to put these initial query results into a temp table and then run a query to select the duplicate lines, tag them, and then update them appropriately. I can do the grouping by the Code field in Chrystal Reports if necessary.

I wonder if this could be done in one query. Thanks for any thoughts on this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-05 : 06:26:04

This is the display issue. You should do this in a front end application

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 08:10:46

this can be very easily achieved in reporting tools like SSRS by applying row groups.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-11 : 02:58:30

quote:
Originally posted by visakh16

this can be very easily achieved in reporting tools like SSRS by applying row groups.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

In Crystal reports there is a feature called Suppress if duplicated

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources