1

I have a table with about 25 columns to signify locations, I was wondering if there was any way to transpose this results into a cleaner layout? What i have in my SQL is along the lines of:

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

This gives me a resulting dataset similar to

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

What i would like to have in the end is

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

No i had a look at Simple way to transpose columns and rows in Sql? which seems to have what i need, but i can't seem to make sense of it in my head because i don't want to transpose the whole table

Any help would be great

Cheers Stephen

Community
  • 1
  • 1
Stephen
  • 11
  • 3
  • if your table was more normalized so that the injuries were in their own child table, and had basically `(victimID, areaID)`, this query would be a simple trivial join/group thing. but since you've got multiple columns, you're stuck... If you're on sqlserver or similar, you can use an "unpivot" query, but otherwise you'd be better off doing the transformation in client-side code. – Marc B Mar 06 '14 at 02:12
  • You could do a monster series of `UNION`s, couldn't you? `SELECT 'Face' as 'Area', sum(...) As 'Count' from tbl_OHS_IncidentsPeople UNION SELECT 'Head' as 'Area', sum(...)...` You get the idea. Feels kind of ugly and I'd check your `explain plan` to consider the impact, but it seems like that would get you the result set you describe. – barry-johnson Mar 06 '14 at 02:15
  • Marc B is absolutely correct that the 'right' answer is normalization. However, my guess is that you're hitting a legacy database and your app isn't the only one using it. He is also correct that doing it on the client side would almost certainly be the most efficient. – barry-johnson Mar 06 '14 at 02:16

1 Answers1

2

One way is to split up the counts by unions like below:

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
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
  • Thanks a lot guys, so the suggestiong would be to have another table for the injury locations, and have something along the lines of a column for the person ID, and a column for each injury location? I have the ability to modify the application if required if it's easier it will be the way to go. I'm trying to avoid having the specify each column incase that changes in the future. – Stephen Mar 11 '14 at 01:55
  • I would have one table to record the HITS (person_id, location_id, date_of_hit), another table for LOCATIONS (location_id, location_name), and another table for PERSONS (person_id, person_name). Then, when you query, use the HITS table, and join with LOCATIONS / PERSONS to get the name of each person and location. – Brian DeMilia Mar 11 '14 at 02:00
  • Thanks, i will look at modifying the application to move the location into a seperaate table. Currently i have one for HITS and one for PERSONS, but will add the LOCATION to this as well. just need to work out how to move the "checkbox" information into a single line. – Stephen Mar 12 '14 at 21:53