bhovious (10/17/2008)
Here's a set based solution using CTEs. Please let us see the execution plans and performance statistics when you have tested them. Best of luck.Bob
I tested your solution for data integrity but it seems it is giving me the wrong output on some other patterns. It is correct on Item #1 data that I supplied but for some other patterns it isn't, there is probably a problem with the logic. The image that I attached on my previous reply would explain how to get the result, I also tried the sample data on that image presentation (attached) and the output is lacking using your suggested solution :(.
Thank you so much for the help.
Here are some other patterns that might help on testing data integrity, logic in getting the output is still the same as stated in the attached image on my previous post.
Pattern #1
INSERT #TempTable
SELECT 1,'1','en','C' UNION ALL
SELECT 2,'1','de','C' UNION ALL
SELECT 3,'1','zh','A' UNION ALL
SELECT 4,'2','en','A' UNION ALL
SELECT 5,'2','en','A' UNION ALL
SELECT 10,'1','zh','A' UNION ALL
SELECT 11,'1','zh','A' UNION ALL
SELECT 15,'1','zh','C' UNION ALL
SELECT 16,'1','de','C' UNION ALL
SELECT 20,'1','en','A' UNION ALL
SELECT 21,'3','en','A' UNION ALL
SELECT 22,'3','de','A' UNION ALL
SELECT 23,'3','en','A' UNION ALL
SELECT 24,'3','de','A' UNION ALL
SELECT 30,'1','en','C' UNION ALL
SELECT 31,'1','en','C'
select * from #temptable
--//Output
SELECT 3 as RowID,'1' as RowKey,'zh' as Locale,'A' as [Action] UNION ALL
SELECT 5,'2','en','A' UNION ALL
SELECT 11,'1','zh','A' UNION ALL
SELECT 15,'1','zh','C' UNION ALL
SELECT 20,'1','en','A' UNION ALL
SELECT 23,'3','en','A' UNION ALL
SELECT 30,'1','en','C'
Pattern #2
INSERT #TempTable
SELECT 1,'1','en','D' UNION ALL
SELECT 2,'1','de','C' UNION ALL
SELECT 3,'1','zh','A' UNION ALL
SELECT 4,'1','zh','A' UNION ALL
SELECT 5,'1','zh','A' UNION ALL
SELECT 10,'1','zh','A' UNION ALL
SELECT 11,'1','zh','C' UNION ALL
SELECT 15,'1','zh','C' UNION ALL
SELECT 16,'1','de','C' UNION ALL
SELECT 20,'3','en','A' UNION ALL
SELECT 21,'3','de','A' UNION ALL
SELECT 22,'3','de','A' UNION ALL
SELECT 23,'3','en','A' UNION ALL
SELECT 24,'3','de','D' UNION ALL
SELECT 30,'1','en','C' UNION ALL
SELECT 31,'1','en','C'
select * from #temptable
--//Output
SELECT 2 as RowID,'1' as RowKey,'de' as Locale,'C' as [Action] UNION ALL
SELECT 3,'1','zh','A' UNION ALL
SELECT 4,'1','zh','A' UNION ALL
SELECT 5,'1','zh','A' UNION ALL
SELECT 10,'1','zh','A' UNION ALL
SELECT 11,'1','zh','C' UNION ALL
SELECT 20,'3','en','A' UNION ALL
SELECT 22,'3','de','A' UNION ALL
SELECT 24,'3','de','D' UNION ALL
SELECT 30,'1','en','C'
Pattern #3
INSERT #TempTable
SELECT 1,'5','en','D' UNION ALL
SELECT 2,'5','de','C' UNION ALL
SELECT 3,'4','zh','C' UNION ALL
SELECT 5,'5','zh','A' UNION ALL
SELECT 6,'4','zh','A' UNION ALL
SELECT 7,'3','zh','A' UNION ALL
SELECT 11,'2','zh','A' UNION ALL
SELECT 15,'1','zh','A' UNION ALL
SELECT 16,'1','zh','A' UNION ALL
SELECT 20,'2','en','C' UNION ALL
SELECT 21,'3','de','C' UNION ALL
SELECT 22,'4','de','A' UNION ALL
SELECT 23,'2','en','A' UNION ALL
SELECT 24,'4','de','A' UNION ALL
SELECT 30,'1','en','C' UNION ALL
SELECT 31,'1','en','C'
select * from #temptable
--//Output
SELECT 2 as RowID,'5' as RowKey,'de' as Locale,'C' as [Action] UNION ALL
SELECT 5,'5','zh','A' UNION ALL
SELECT 16,'1','zh','A' UNION ALL
SELECT 20,'2','en','C' UNION ALL
SELECT 22,'4','de','A' UNION ALL
SELECT 24,'4','de','A' UNION ALL
SELECT 30,'1','en','C'