Pascal H Pascal H - 3 months ago 9
SQL Question

SQL Server: Create a specific ID counter

I have the following view in MS SQL Server:

TestR SW ID
--------------
Test1 A P1
Test1 A P2
Test2 B P1
Test2 B P2
Test5 A P1
Test5 A P2
Test5 B P1
Test5 B P2
Test3 A P3
Test3 A P4
Test3 B P3
Test3 B P4
Test4 A P5
Test6 A P1


As you can see for the Test1,2,5 I have different SW but the same ID (P1;P2). In Test3 I have different SW integrated and the ID (P3;P4)twice. Test4 has only one SW and ID. Test6 is similar to Test1,2,5 but it has only the ID P1.

What I want to accomplish is to integrate a specific increment IDCount:
As an example I want not to increment IDCount when Testn.ID =Testn.ID, because they have the same IDs. In Test3 I increment one, because the ID do not fit to Test1,Test2...In Test4 I also have to increment. In Test6 the ID P1 occured in Test1,Test2 but the dataset is not the same. The column SW has no impact on the IDCount.

IDCount TestR SW ID
-------------------------
1 Test1 A P1
1 Test1 A P2
1 Test2 B P1
1 Test2 B P2
1 Test5 A P1
1 Test5 A P2
1 Test5 B P1
1 Test5 B P2
2 Test3 A P3
2 Test3 A P4
2 Test3 B P3
2 Test3 B P4
3 Test4 A P5
4 Test6 A P1


I hope you can understand my issue and I would be grateful for some help.Currently I used the commando: Rank() OVER (ORDER BY TestR) AS IDCount but this works only for Test3. I think the main problem is to check whether the different TestR had the same IDs.

Answer

Ordering by setcode which is concatenation of ordered distinct IDs

with tbl(TestR,SW,ID) as(
    select * 
    from (
        values
         ('Test1','A','P1')
        ,('Test1','A','P2')
        ,('Test2','B','P1')
        ,('Test2','B','P2')
        ,('Test5','A','P1')
        ,('Test5','A','P2')
        ,('Test5','B','P1')
        ,('Test5','B','P2')
        ,('Test3','A','P3')
        ,('Test3','A','P4')
        ,('Test3','B','P3')
        ,('Test3','B','P4')
        ,('Test4','A','P5')
        ,('Test6','A','P1')
    ) t(TestR,SW,ID)
)
, sets  as (
    select  t1.*, 
         ( select distinct '-'+t2.ID 
           from tbl t2 
           where t2.TestR = t1.TestR
           order by '-'+t2.ID 
           for xml path('')
          ) setcode
    from tbl t1 
)
select dense_rank() over(order by setcode) as IDCount, TestR, SW, ID, setcode
from sets
order by dense_rank() over(order by setcode),TestR;

EDIT
A bit different order, by min(TestR) in the group

with ...
)
, sets  as (
    select  t1.*, 
         ( select distinct '-'+t2.ID 
           from tbl t2 
           where t2.TestR = t1.TestR
           order by '-'+t2.ID 
           for xml path('')
          ) setcode
    from tbl t1 
), keys as (
    select *, min(TestR) over(partition by setcode) key1
    from sets
)
select dense_rank() over(order by key1) as IDCount, TestR, SW, ID, setcode
from keys
order by dense_rank() over(order by key1),TestR;