Display data from single table where column values match

Question

I have a table called 'Artists' which has columns Artist_ID, Artist_Name, Artist_Genre and have been trying to figure out how to list the Artists where they share the same Genre.

I think I might need a self join but can't quite get there. Can you please help?

Desired Output
Artist Artist 2 Shared Genre 
A      B        Classic 
B      C        Pop 

Answer 1

In SQL Server this would do what you ask, but I don't think it is really what you are asking.

CREATE TABLE Artists(Artist_ID INT, Artist_Name VARCHAR(100), Artist_Genre VARCHAR(20))

INSERT INTO Artists VALUES
(1,'A','Classic')
,(2,'B','Classic')
,(2,'B','Pop')
,(3,'C','Pop')

SELECT a.Artist_Name [Artist 1]
      ,b.Artist_Name [Artist 2]
      ,a.Artist_Genre [Shared Genre]          
  FROM Artists a
       INNER JOIN
       Artists b ON a.Artist_Genre = b.Artist_Genre
 WHERE a.Artist_ID < b.Artist_ID

EDIT: I think it is worth showing you here as well that you can group by and generate a artist list. In sql server the syntax is a mess, but it get the job done.

SELECT b.Artist_Genre,
       STUFF((SELECT ',' + CAST(Artist_Name AS VARCHAR(MAX))
                FROM Artists a
               WHERE a.Artist_Genre = b.Artist_Genre
               ORDER BY Artist_Name
                 FOR XML PATH('')),1,1,'') Artist_List
  FROM Artists b
 GROUP BY b.Artist_Genre