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
@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?
No my issue is if you look at CurrentYrMnth through the %Change column. The calmnth is forcing those to be repeated.
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?
@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?
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 @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.
