ID列需要显示出与specilal值

Question

我在这个表标识一个表测试列，FuelId和FuelDesc列there.values像下表，基于前三我需要创建列像下面的表，请帮助我得到。

ID FuelID FuelDesc 
100 01  Elec  
101 02  Gas  
102 02  Gas  
100 02  Gas  
101 01  Elec  
103 01  Elec  

O/P： -

ID Pamenttype  
100 Both   
101 Both  
102 Gas   
103 Elec 

Answer 1

SELECT ID, CASE WHEN rowCount = 1 THEN FuelDesc ELSE 'Both' END AS Pamenttype 
FROM 
(SELECT ID, MAX(FuelDesc) AS FuelDesc, COUNT(*) AS rowCount 
FROM mytable 
GROUP BY ID) 

Answer 2

可以使用CASE表达，与COUNT和MAX功能的帮助：

SELECT 
    ID, 
    PaymentType = CASE WHEN COUNT(FuelId) > 1 THEN 'Both' ELSE MAX(FuelDESC) END 
FROM test 
GROUP BY ID 

Answer 3

您可以定义Both为有一个记录FuelID = '01'和另一个机智h FuelID = '02'。假设{ID，FuelID}在表测试独特：

with BothPaymentTypes as (
    select t1.ID 
    from Test t1 
    join Test t2 on t1.ID = t2.ID 
    where t1.FuelID = '01' 
    and t2.FuelID = '02' 
) 
select ID, 'Both' as Pamenttype 
from BothPaymentTypes 
union all 
select ID, FuelDesc 
from Test 
where ID not in (select ID from BothPaymentTypes) 

[SQL Fiddle Demo]

---

或者，你能避免使用COUNT和HAVING子句中自联接 - 再次假设{ID， FuelID}是唯一的：

with BothPaymentTypes as (
    select ID 
    from Test 
    group by ID 
    having count(*) = 2 
) 
select ID, 'Both' as Pamenttype 
from BothPaymentTypes 
union all 
select ID, FuelDesc 
from Test 
where ID not in (select ID from BothPaymentTypes) 

[SQL Fiddle Demo]

Answer 4

WITH fuelCount 
AS 
(
    SELECT ID, COUNT(FuelID) AS fuelCount 
    FROM Test GROUP BY ID 
) 
SELECT fc.ID, 
CASE 
    WHEN fc.fuelCount > 1 THEN 'BOTH' 
    ELSE (SELECT t.FuelDesc FROM Test t WHERE t.ID = fc.ID) 
END AS Pamenttype 
FROM fuelCount fc