SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Changing column values in duplicate rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smh
Yak Posting Veteran

USA
91 Posts

Posted - 09/07/2013 :  16:27:22  Show Profile  Reply with Quote
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.

Edited by - smh on 09/07/2013 16:28:19

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 11/05/2013 :  06:26:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
This is the display issue. You should do this in a front end application

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/05/2013 :  08:10:46  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 11/11/2013 :  02:58:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000