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 id,count(l1),l1
from @table1
where id in ('shine','revolves') and id in ('sun','moon','earth')

Msg 8120 Level 16 State 1 Line 69
Column '@table1.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.