SELECT DISTINCT for data groups

Question

I have the following table:

ID  Data
1   A
2   A
2   B
3   A
3   B
4   C
5   D 
6   A
6   B

etc .. In other words, I have data groups on an identifier. You will notice that the data group (A, B) occurs several times. I need a query that can identify individual groups of data and their number, for example:

DataID     Data
101        A
102        A
102        B
103        C
104        D

Thus, DataID 102 will resemble data (A, B), DataID 103 will resemble data (C), etc. To be able to rewrite my source table in this form:

ID   DataID
1    101
2    102
3    102
4    103
5    104
6    102

How can i do this?

---

PS. Code for creating the first table:

CREATE TABLE #t1 (id INT, data VARCHAR(10))
INSERT INTO #t1
SELECT 1, 'A'
UNION ALL SELECT 2, 'A'
UNION ALL SELECT 2, 'B'
UNION ALL SELECT 3, 'A'
UNION ALL SELECT 3, 'B'
UNION ALL SELECT 4, 'C'
UNION ALL SELECT 5, 'D'
UNION ALL SELECT 6, 'A'
UNION ALL SELECT 6, 'B'

Answer 1

-, , ( CLR- ). , row_number() dense_rank() . .

with groupings as (
select concat(data) groups
from Table1
group by ID
)
select groups, rownumber() over () from groupings

Answer 2

CASE , .

, .

SELECT     
    id, 
     MAX(CASE data WHEN 'A' THEN data ELSE '' END) + 
     MAX(CASE data WHEN 'B' THEN data ELSE '' END) + 
     MAX(CASE data WHEN 'C' THEN data ELSE '' END) + 
     MAX(CASE data WHEN 'D' THEN data ELSE '' END) AS DataGroups
FROM  t1
GROUP BY id

ID  DataGroups
1   A
2   AB
3   AB
4   C
5   D
6   AB

, "" , .

, . , , , 1000, , : -)

LuckyLuke , , , , , .

Answer 3

From your sample data (adding the missing 2, 'A' tuple, the following gives numbered (and unique) data:

with NonDups as (
select t1.id
from #t1 t1 left join #t1 t2
on t1.id > t2.id and t1.data = t2.data
group by t1.id
having COUNT(t1.data) > COUNT(t2.data)
), DataAddedBack as (
    select ID,data
    from #t1 where id in (select id from NonDups)
), Renumbered as (
    select DENSE_RANK() OVER (ORDER BY id) as ID,Data from DataAddedBack
)
select * from Renumbered

Donation:

1          A
2          A
2          B
3          C
4          D

I think this is a question of relational division in order to match the rows from this output with the rows in the source table.

Answer 4

Just to share my own dirty solution that I am currently using:

SELECT DISTINCT t1.id, D.data
FROM #t1 t1
CROSS APPLY ( 
    SELECT CAST(Data AS VARCHAR) + ','
    FROM #t1 t2
    WHERE t2.id = t1.id
    ORDER BY Data ASC
    FOR XML PATH('') )  
D ( Data )

And then move on to the LuckyLuke analog solution.