我有一个带有大约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 |
不,我看着简单的方法来传输SQL中的列和行?似乎有我需要的东西,但是我似乎无法在脑海中理解它,因为我不想转置整个桌子
任何帮助都很好
欢呼斯蒂芬
一种方法是按照以下的工会拆分计数:
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