SELECT [Sheet1$].[Fruit Batch ID] AS [Fruit Batch ID],
  [Sheet1$].[Number of Bad Fruit_Bruised] AS [Value],
  "Bruised Fruit" AS [Reason]
FROM [Sheet1$]

UNION ALL

SELECT [Sheet1$].[Fruit Batch ID] AS [Fruit Batch ID],
  [Sheet1$].[Number of Bad Fruit_Rotten] AS [Value],
  "Rotten Fruit" AS [Reason]
FROM [Sheet1$]

UNION ALL

SELECT [Sheet1$].[Fruit Batch ID] AS [Fruit Batch ID],
  [Sheet1$].[Number of Bad Fruit_Too Big] AS [Value],
  "Too Big" AS [Reason]
FROM [Sheet1$]

UNION ALL

SELECT [Sheet1$].[Fruit Batch ID] AS [Fruit Batch ID],
  [Sheet1$].[Number of Bad Fruit_Too Small] AS [Value],
  "Too Small" AS [Reason]
FROM [Sheet1$]