question

sqlLearner 1 avatar image
sqlLearner 1 asked

How to eliminate duplicate rows

Hi, I am having a problem with duplicate rows. I will show an example of my table which will make things clearer but this is my basic problem: I want to get the Annual Revenue each year for an accountID. I also have calculated the percenatge chage in product usage. So the probelm is when it shows my Annual Revenue it is duplicating the year column in the product percentage which you will see in this example. The Calmonth corresponds to AnnRev, AcctID Name Cntry CalMnth1 ARev Calmnth2 Arev CurYrMth CurMthTot PrevYrMnth PrevMnthTot %Change 123456 ABC US 201105 1500 201005 2000 201105 4 201005 9 -55.56 654321 BCA US 201105 3200 201005 3200 201105 5 201005 4 -75.00 Any Thoughts
sqldelete-duplicates
13 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@sqllearner The data in your sample dioes not show any duplicates. Records 1 and 2 are different because these are for a different year (CalMnth values are 201005 and 201105). Records 3 and 4 seem to be for the same year which is probably not desirable but then the real question is this: if you want to exclude one of the records from record 3 and record 4 bunch then which one of the two would you like to exclude?
0 Likes 0 ·
No my issue is if you look at CurrentYrMnth through the %Change column. The calmnth is forcing those to be repeated.
0 Likes 0 ·
@sqlLearner **CalMnth** is not responsible for repeating CurrentYrMnth through the %Change columns in case of the AccctID = 654321 (records 3 and 4 in your sample). Both of these have identical value in **CalMnth** column, namely **201105**
0 Likes 0 ·
201105 was a typo when i entered the sample data I updated the Calmnth column..So are you saying there is nothing I can do to keep the Columns CurrYrMnth THROUGH %Change from showing the same 2 rows for every record?
0 Likes 0 ·
@sqlLearner OK, I see that you updated the sample. Now it appears that you want to have just one record from the first 2 and one more record from the last 2. Do you know the rule on which to base such a decision? In oher words which records you consider as unwanted and why?
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
There are basically 2 ways of doing it: using static PIVOT or a good old case statement. The downside of the static PIVOT is that you can only PIVOT by the list from one column, so if you need more than that (2 in your case, namely CalMnth and ARev) then you need to select the data twice. Before pivoting your results, you also need to have some common data, such as partitioned numbering. This way you can use that column's values as the base for your pivot. Here isthe sample, which will work once you replace the table name I used to whatever you actually have: ;with records as ( select AcctID, Name, Cntry, CalMnth, ARev, CurYrMth, CurMthTot, PrevYrMnth, PrevMnthTot, [%Change], -- this is a column which you can use for pivoting results row_number() over (partition by AcctID order by CalMnth desc) N from YourTable ) select a.AcctID, a.Name, a.Cntry, a.[1] CalMnth1, b.[1] Arev1, a.[2] CalMnth2, b.[2] Arev2, a.CurYrMth, a.CurMthTot, a.PrevYrMnth, a.PrevMnthTot, a.[%Change] from ( /* this is first pivot, everything but ARev is needed */ select * from ( select AcctID, Name, Cntry, CalMnth, CurYrMth, CurMthTot, PrevYrMnth, PrevMnthTot, [%Change], N from records ) src pivot (max(CalMnth) for N in ([1], [2])) pvt ) a inner join ( /* this is second pivot, AcctID, ARev, and N is all you neeed */ select * from ( select AcctID, ARev, N from records ) src pivot (max(ARev) for N in ([1], [2])) pvt ) b on a.AcctID = b.AcctID; Oleg
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 @oleg.. anyway I think the real solution will be much easier as @sqlLearner mentioned, that the data are coming from two temp tables. So his original result was probably by join of the two tables. If he posted the structure of those tables, the solution could be even simplified.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.