Transpose table including headers

Solution 1:

If your database supports lateral joins and the values() row constructor, you can unpivot and aggregate as follows:

select x.entity, count(x.val) records
from account a
cross apply (values ('contactid', a.contactid), ('firstname', a.firstname), ('last_name', a.lastname)) as x(entity, val)
group by x.entity

Some databases use cross join lateral instead of cross apply.

A more portable approach (although less efficient) is union all:

select 'contactid' entity, count(contactid) from account
union all
select 'firstname', count(firstname) from account 
union all
select 'lastname', count(lastname) from account