转置SQL结果

Question

我有一个大约有25列的表格来表示位置，我想知道是否有任何方法可以将这个结果转换为一个更干净的布局？我的SQL中包含的内容大致如下：

SELECT sum(isnull(p.[injuryFace],0)) AS [Face]
  ,sum(isnull(p.[InjuryHead],0)) AS [Head]
  ,sum(isnull(p.[InjuryEye],0)) AS [Eye]
  ,sum(isnull(p.[InjuryLeftFinger],0)) AS [Finger - Left]
  ,....
FROM tbl_OHS_IncidentsPeople P

这给出了一个结果数据集，类似于

Face | Head | Eye | Finger - Left | .... |
---------------------------------------------
  0  |  1   |  2  |      0        | ...  |

我最终想要的是

Area          | Count |
------------------------
Face          |   0   |
Head          |   2   |
Eye           |   3   |
Finger - Left |   0   | 

不，我看过Simple way to transpose columns and rows in Sql?，它似乎有我需要的东西，但我似乎无法在我的头脑中理解它，因为我不想颠倒整个表

任何帮助都是最好的

干杯斯蒂芬

Answer 1

一种方法是按工会划分计数，如下所示：

select 'Face' as area, sum(isnull(p. [ injuryFace ], 0)) as location
  from tbl_OHS_IncidentsPeople P
union all
select 'Head' as area, sum(isnull(p. [ InjuryHead ], 0))
  from tbl_OHS_IncidentsPeople P
union all
select 'Eye' as area, sum(isnull(p. [ InjuryEye ], 0))
  from tbl_OHS_IncidentsPeople P
union all
select 'Finger - Left' as area, sum(isnull(p. [ InjuryLeftFinger ], 0))
  from tbl_OHS_IncidentsPeople P