March 24, 2010 at 10:59 am
Hi,
Within my table (TableA) I have a column (Group_Id) which needs to be populated with a integer to identify each set of values held in columns 'AB_Id', 'Priority' & 'Analysis_Code' collectively.
EXAMPLE
Before:
AB_Id, Priority, Analysis_Code, Group_Id
1212, 3, 1902, 0
1212, 3, 1902, 0
1212, 3, 1902, 0
1212, 3, 1902, 0
1214, 3, 1989, 0
1214, 3, 1902, 0
1215, 3, 2002, 0
1215, 3, 2002, 0
1216, 2, 2004, 0
1216, 3, 2004, 0
After:
AB_Id, Priority, Analysis_Code, Group_Id
1212, 3, 1902, 1
1212, 3, 1902, 1
1212, 3, 1902, 1
1212, 3, 1902, 1
1214, 3, 1989, 2
1214, 3, 1902, 3
1215, 3, 2002, 4
1215, 3, 2002, 4
1216, 2, 2004, 5
1216, 3, 2004, 6
Any ideas how I achieve this?
Thanks in advance,
March 24, 2010 at 11:26 am
I think this will do it for you.
-- see how the test data can be put into a table? It sure helps others to not have to do this!
declare @test table (AB_Id int, Priority int, Analysis_Code int, Group_Id int)
insert into @test
SELECT 1212, 3, 1902, 0 UNION ALL
SELECT 1212, 3, 1902, 0 UNION ALL
SELECT 1212, 3, 1902, 0 UNION ALL
SELECT 1212, 3, 1902, 0 UNION ALL
SELECT 1214, 3, 1989, 0 UNION ALL
SELECT 1214, 3, 1902, 0 UNION ALL
SELECT 1215, 3, 2002, 0 UNION ALL
SELECT 1215, 3, 2002, 0 UNION ALL
SELECT 1216, 2, 2004, 0 UNION ALL
SELECT 1216, 3, 2004, 0
;WITH CTE AS
(select AB_Id, Priority, Analysis_Code,
Group_Id = DENSE_RANK() OVER (ORDER BY AB_ID, Priority, Analysis_Code)
from @test)
UPDATE t
SET Group_Id = CTE.Group_Id
FROM @test t
JOIN CTE
ON t.AB_Id = CTE.AB_Id
AND t.Priority = CTE.Priority
AND t.Analysis_Code = CTE.Analysis_Code
select * from @test
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply