SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @Table1 AS TABLE (
    id int, 
    l1 varchar(20)
);

INSERT INTO @Table1 VALUES
(1,'sun'),
(2,'shine'),
(3,'moon'),
(4,'light'),
(5,'earth'),
(6,'revolves'),
(7,'flow'),
(8,'fire'),
(9,'fighter'),
(10,'sun'),
(10,'shine'),
(11,'shine'),
(12,'moon'),
(1,'revolves'),
(10,'revolves'),
(2,'air'),
(3,'shine'),
(4,'fire'),
(5,'love'),
(6,'sun'),
(7,'rises');

/* OPERATION 1
fetch all distinct ID's that has values from List 1
List1
sun
moon
earth

Initial OUTPUT1:
distinct_id list1_value
1           sun
3           moon
5           earth
10          sun
12          moon
6           sun
*/

/*OPERATION2
fetch all the id, count_of_list2_values, list2_values  
based on the id's that we recieved from OPERATION1

List2
shine
revolves

Expected Output:
id count_of_list2_values, list2_values  
1       1                 revolves
3       1                 shine
5       0                 NULL
10      2                 shine,revolves
12      0                 NULL
6       1                 revolves
*/

/*My query:*/

select
    l1.id,
    l1.l1,
    l2.cnt as count_of_list2_values,
    l2.l1 as list2_values  
from @Table1 as l1
    outer apply (
        select
            count(*) as cnt,
            string_agg(tt.l1, ',') as l1
        from @Table1 as tt
        where
            tt.l1 in ('shine','revolves') and
            tt.id = l1.id
    ) as l2
where
    l1.l1 in ('sun','moon','earth')

id	l1	count_of_list2_values	list2_values
1	sun	1	revolves
3	moon	1	shine
5	earth	0	null
10	sun	2	shine,revolves
12	moon	0	null
6	sun	1	revolves