getting total use of values across multiple columns

[Movian]

Our system has a table called ledger

services can have up to 12 additional descriptor codes these are stored as 12 fields in the same table marked dx1, dx2, dx3 etc

I have worked up a script with the basic essence of what I am trying to get for a single column


SELECT dx1, count(dx1) FROM Ledger group by dx1

I tried using UNION but that lead to issues getting the count

however I need to include the values from the other columns so I get a single list with total time each code is used across all 12 columns

(I will also be adding a date range after this on the procedure date column but just trying to get the basics in first).

[Movian]

Ok never mind looks like I found the function I needed

UNPIVOT

the final script was


SELECT ICD9, Count(*) as TotalUses FROM Ledger
UNPIVOT( ICD9 FOR DX in ([dx1], [dx2], [dx3], [dx4], [dx5], [dx6], [dx7], [dx8], [dx9], [dx10], [dx11], [dx12])) as p
WHERE ProcedureDate >= '01/01/2001' AND ProcedureDate <= '01/01/2016'
group by ICD9