In your output data extension, creat the necessary fields as below and make EmailAddress and ListName primary keys.
The below SQL should list you all subscribers in your DE Name alongside the ListName and their status in that List
from [DE Name] as a
Left join _ListSubscribers as l on a.EmailAddress = l.EmailAddress
You could then perform a filter to find those contacts with the subscription to the relevant list.
If you wanted to have a single contact record, and a pivot of lists they are subscribed to; then the approach I would take is to store the list names in a concatenated string (single field - text unlimited) called ListNames. You would otherwise need to create a data extension field for each/every list you create and this would get unmanageable.
firstname.lastname@example.org | John | Doe | ListA,ListC,ListD
Below is example code - you might need to play about with it.
Basically you are wanting to use the XML path to pivot the results into a string
(SELECT ',' + ListNames.ListName
INNER JOIN [DE Name] ON on a.EmailAddress = l.EmailAddress
FOR XML PATH('')),2,200000) AS ListNames
FROM [De Name]
INNER JOIN _ListSubscribers ON a.EmailAddress = l.EmailAddress
WHERE ( ListNames.Status = 'Active' )
GROUP BY [DE Name].EmailAddress