If the number of elements per ID is always 3 then you can pivot, group by yhe pivoted columns and count the distinct IDs.

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE trial

(

ID int,

Val varchar(2),

PRIMARY KEY (ID, Val)

);

INSERT INTO trial

(ID, Val)

VALUES

(1, 'A'),

(1, 'B'),

(1, 'C'),

(2, 'C'),

(2, 'B'),

(2, 'A'),

(3, 'A'),

(3, 'B'),

(3, 'X'),

(4, 'A'),

(4, 'Z'),

(4, 'Y'),

(5, 'A'),

(5, 'B'),

(5, 'C');

GO

WITH C1 AS (

SELECT

ID,

MIN(CASE WHEN rn = 1 THEN Val END) AS Val1,

MIN(CASE WHEN rn = 2 THEN Val END) AS Val2,

MIN(CASE WHEN rn = 3 THEN Val END) AS Val3

FROM

(

SELECT

ID,

Val,

ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Val) AS rn

FROM

trial

) AS T

GROUP BY

ID

)

SELECT

Val1,

Val2,

Val3,

COUNT(DISTINCT ID) AS cnt,

COUNT(*) OVER() AS DistinctGroups

FROM

C1

GROUP BY

Val1,

Val2,

Val3

ORDER BY

Val1,

Val2,

Val3;

GO

DROP TABLE trial;

GO

If the number of elements is variable then things get complicated and having a string aggregation could be handy or may be go for dynamic pivoting (ouch!).