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 

Answer 2

You can use case when statement like:

CREATE TABLE AR
(
    ABC DECIMAL(4,2),
    INTERNAL DECIMAL(4,2),
    External1 DECIMAL(4,2)
)

INSERT INTO AR
VALUES (5.6, 5.9, 6.3), (4, 4.4, 4.8)

SELECT 
    CASE 
       WHEN ABC = 5.60 THEN 'X'
       ELSE 'Y' 
    END, * 
FROM 
    AR

Answer 3

--Added all records in #temp table
SELECT 
--CASE WHEN ABC=5.60 THEN 'X'
--ELSE 'Y' END,
 * INTO #temp FROM AR

 --create surrogate key using identity column
 Alter table #temp
 add idnty int identity(1,1) 

--add condition using identity column
SELECT 
CASE WHEN idnty=1 THEN 'X'
ELSE 'Y' END, ABC, INTERNAL, External1 FROM #temp

drop table #temp