Matching multiple values in the same SQL query

Question

I've a table with the following structure.

Table Name: CustomerStocks

Structure 
Name                     Varchar(25) 
StockSymbol              Varchar(4)

Following is the sample of table contents

Name StockSymbol

Sam    AAPL
Sam    AMZN
Sam    GOOG

Judy   AAPL
Judy   AMZN

Jen    AMZN

Brian  GOOG
Brian  MSFT

The goal is given a customer name, how do I find out the list of other customers who has similar portfolio. In other words, all the equities of original customers must be present.

Thus, if I'm looking for customers who has similar portfolio of Jen, then the result will be Judy and Sam.

If I'm searching for customers who has similar portfolio of Judy, then the result will be Sam.

However, if I'm searching for customers with Brian's portfolio, it can return either no rows or just Brian.

Is this possible? If so ,how do I write a sql query to achieve this?

Any help is truly appreciated on this.

Answer 1

(one minor adjustment later...)

I've not tested this but something like...

DECLARE @name VARCHAR(24) = 'Judy';

WITH cte_stocks AS (
    SELECT StockSymbol
    FROM CustomerStocks
    WHERE Name = @name
)
SELECT Name
FROM CustomerStocks cs
LEFT JOIN cte_stocks a ON cs.StockSymbol = a.StockSymbol
GROUP BY cs.Name
HAVING COUNT(DISTINCT a.StockSymbol) = (SELECT COUNT(1) FROM cte_stocks)

...might do it