DECLARE @RegionCounts Table
(
TYear char(2),
Region varchar(30),
RowCnt int
)

Insert into @RegionCounts values ('13','Latam',100)
Insert into @RegionCounts values ('13','NOAM',200)
Insert into @RegionCounts values ('14','Latam',300)
Insert into @RegionCounts values ('14','NOAM',400)

select * from @RegionCounts

Thank you all for your comments and solutions. I was able to solve my problem as below.
In the procedure, used row_number to generate row numbers within the group by...

No. do not want the sum of qtySent.
There will be bunch of other columns like DispatchDate, Location for each of those rows which have different values per row.

Super..!! This works exactly, as needed. Thank you.

You can do a batch delete of the rows, in the batch increments.

USE AdventureWorks2008R2;

GO

--Delete in batches of 10000 rows

While @@ROWCOUNT <> 0

DELETE Top 10000 * FROM Purchasing.PurchaseOrderDetail

WHERE DueDate =...

Thank you. The solution worked.

Thank you. The solution worked and gave the desired results.

Jeff, Here is the code for split function..

CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s-2 VARCHAR(MAX))

RETURNS @t TABLE

(

val...

The solution from MelroyV, worked for me. Thanks a lot, melroy.

Thank you Sean for your inputs/suggestions.

Your suggested solution was close too, but I was looking for traversed results....

The query worked. Made a minor modification, and it gave me the desired results.

SELECT itemNoAS Item,

CatAS CatID,

SubCatAS SubCatID

FROM #Test

CROSS APPLY( VALUES

( Cat1, SubCat11),

( Cat1, SubCat12),

( Cat2, SubCat21),

(...

Thank you very much. The solution worked, and this was exactly what I was looking for..!!

Thank you very much. The script worked pretty well, on my actual tables with minor tweaking.

How about using row_number() over (order by Userid) as rownum, and do an order by on this Row Num.

My Actual tables has more than 50 columns, of which only 3 columns needs to be distinct, on any row, and has many data rows. Tried to simulate the problem...