SQL query to retrieve Expected result

Hi , From the below table and insert value script i need to derive the expected result value.  Required help in this .   CREATE TABLE #data(MainID int NOT NULL, Component varchar(10) NOT NULL, ...