Rotate SQL data separated by comma in SQL "in" statement

Question

First statement:

Select GroupMember
FROM Group 

Result: Los Angeles, San Diego (this is a row)

Second statement:

 SELECT *
 FROM Store_Information 
 WHERE Store_Name IN ('Los Angeles', 'San Diego');

How to turn Los Angeles, San Diego (one information) into IN ("Los Angeles", "San Diego")?

Answer 1

Use internal query

SELECT * FROM Store_Information
WHERE Store_Name IN (Select GroupMember FROM Group)

Answer 2

you can write a query like

SELECT * 
FROM 
Store_Information WHERE Store_Name IN (
SELECT Split.a.value('.', 'VARCHAR(100)') AS String   
       FROM  (SELECT  CAST ('<M>' + REPLACE(groupname, ',', '</M><M>') + '</M>' 
                             AS XML) AS groupname       
               FROM  Group) 
       AS A 
CROSS APPLY groupname.nodes ('/M') AS Split(a))

Answer 3

, - :

SELECT s.*
FROM Store_Information s
JOIN Group g ON g.GroupMember = s.Store_Name

Answer 4

Select GroupMember
FROM Group 

1 , -, -

,

SELECT * FROM Store_Information
WHERE Store_Name IN (Select ''' + REPLACE(GroupMember,',',''',''') + ''' FROM Group)

• " Los
• replace, → ','
• "