将列标题转换为行数据 - SQL

Question

MySQL查询的打印结果是这样的

North   South   West    East    Central
0       280     0       41      36

但我希望它像这样

North    0
South    280
West     0
East     41
Central  36

SQL：

Select  Count(Case When Region=1 Then 1 Else Null End)[North],
    Count(Case When Region=2 Then 1 Else Null End)[South],
    Count(Case When Region=3 Then 1 Else Null End)[West],
    Count(Case When Region=4 Then 1 Else Null End)[East],
    Count(Case When Region=5 Then 1 Else Null End)[Central] 
    From ATM Where ATMStatus=0 And Bank=1

- user1194919

---

ATM表的模式是什么？ - Giacomo Degli Esposti

我想要列标题的行，以及它们对应的值在其前面，而不是列和它们的值下方。 - user1194919

Answer 1

使用Group by

SELECT 
   CASE Region 
    WHEN 1 THEN 'North'
    WHEN 2 THEN 'South' 
    WHEN 3 THEN 'West'
    WHEN 4 THEN 'East'
    WHEN 5 THEN 'Central' END AS Region  
    , COUNT(ID)   --or your primary key if it is different
FROM ATM
WHERE ATMStatus = 0 AND Bank = 1
GROUP BY Region

- András Ottó

Answer 2

 select case Region
     when 1 then 'North'
     when 2 then 'South'
     etc

 end, count(*)
 From ATM Where ATMStatus=0 And Bank=1  
 group by Region

你有区域表吗？这会让事情变得更简单，特别是如果0行很重要的话。

- podiluska