I want to transform my Table from first displayed form to second displayed form

Question

My query is :

SELECT 
    ROUND(AVG([Q]), 1) AS ABC, 
    (SELECT ROUND(AVG([Q]), 1) 
     FROM [Apple] 
     WHERE [Name] = 'DND' AND [Color] = 'Blue') AS 'Internal',
    (SELECT ROUND(AVG([Q]), 1) 
     FROM [Apple] 
     WHERE [Name] = 'DND' AND [Color] = 'Green') AS EXTERNAL
FROM
    [Apple] 
WHERE
    [Fruit] = '125' 

UNION ALL

SELECT 
    ROUND(EXP(AVG(LOG([Q]))), 1) AS ABC,
    (SELECT ROUND(EXP(AVG(LOG([Q]))), 1)  
     FROM [Apple] 
     WHERE [Name] = 'DND' AND [Color] = 'Blue') AS 'Internal',
    (SELECT ROUND(EXP(AVG(LOG([Q]))), 1)  
     FROM [Apple] 
     WHERE [Name] = 'DND' AND [Color] = 'Green') AS EXTERNAL
FROM 
    [Apple] 
WHERE 
    [Q] != 0 

Result:

ABC   Internal  EXTERNAL
-------------------------
5.6   5.9           6.3
4     4.4           4.8

and expected result is :

        ABC    Internal  EXTERNAL
---------------------------------
X       5.6    5.9       6.3
Y       4.1    4.4       4.8

Please provide the query for how to add names for the Rows for the result set obtained in SQL Server.

Answer 1

maybe this is what you need

SELECT 
'first' as ResultSet,
ROUND(AVG([Q]), 1) AS ABC, 
(SELECT ROUND(AVG([Q]), 1) 
 FROM [Apple] 
 WHERE [Name] = 'DND' AND [Color] = 'Blue') AS 'Internal',
(SELECT ROUND(AVG([Q]), 1) 
 FROM [Apple] 
 WHERE [Name] = 'DND' AND [Color] = 'Green') AS EXTERNAL
FROM
    [Apple] 
WHERE
    [Fruit] = '125' 

UNION ALL

SELECT 
'second' as ResultSet,
    ROUND(EXP(AVG(LOG([Q]))), 1) AS ABC,
    (SELECT ROUND(EXP(AVG(LOG([Q]))), 1)  
     FROM [Apple] 
     WHERE [Name] = 'DND' AND [Color] = 'Blue') AS 'Internal',
    (SELECT ROUND(EXP(AVG(LOG([Q]))), 1)  
     FROM [Apple] 
     WHERE [Name] = 'DND' AND [Color] = 'Green') AS EXTERNAL
FROM 
    [Apple] 
WHERE 
    [Q] != 0