SQL Query Convert columns to rows

Question

select sum(cases) as [Total cases], sum(deaths) as [Total deaths] 
FROM [myschema].[metrics]

Can we get by pivot/unpivot function?

Answer 1

Use union all:

select 'total cases' info, sum(cases) cnt from metrics
union all select 'total deaths', sum(deaths) from metrics

Or, if your database supports lateral joins:

select x.info, sum(x.cnt) cnt
from metrics m
cross join lateral (values ('total cases', m.cases), ('total deaths', m.deaths)) as x(info, cnt)
group by x.info

Answer 2

The simplest generic way in is union all:

select 'Total Cases'  as which, sum(cases) 
from [myschema].[metrics]
union all
select 'Total Deaths' as which, sum(deaths)
from [myschema].[metrics];

The square braces look like SQL Server. I would recommend a lateral join in that case:

select v.*
from (select sum(cases) as TotalCases, sum(deaths) as TotalDeaths 
      from [myschema].[metrics]
     ) m cross apply
     (values ('TotalCases', TotalCases), ('TotalDeaths', TotalDeaths)
     ) v(which, cnt);

This reads the table only once. And if the table is really a more complicated query (say a view or CTE), the performance gain can be substantial.

Answer 3

You could use UNPIVOT to get the desired result also:

   SELECT
    *
FROM
    (
        SELECT
            SUM(cases)      AS total_cases,
            SUM(deaths)     AS total_deaths
        FROM
            myschema.metrics
    ) UNPIVOT ( value
        FOR category
    IN ( total_cases,
         total_deaths ) );
     

The output of the above will be:

Category                Value
Total_cases             1234
Total_deaths            123