How to I Pivot this table?

Question

I have created a table that looks something like this:

7column_table

I want to Pivot this table such that it looks like;

3 column table

I have tried some other posts about PIVOTs but I could not understand how to do it with more than 2 columns, nor when the values inside the table are sums from different variables.

Most people here want sample table data and expected result as formatted text, not as images (or links to images.) — jarlh, Oct 30 '19 at 13:27
Which dbms are you using? (Some products have built-in PIVOT functionality, while others haven't.) — jarlh, Oct 30 '19 at 13:28
I am using SSMS. Apologies for the images, this is my first post and I could not figure out the formatting for typing out tables. I will learn how to do this properly when I edit this later. — Clifford Piehl, Oct 30 '19 at 13:53
SSMS is not a database product. It's a SQL client application that connects _to_ a database server. But as it only works with Microsoft SQL Server, it's safe to assume you are using that. — , Oct 30 '19 at 14:02

score 1 · Accepted Answer · answered Oct 30 '19 at 13:35

You want to transpose the recordset (unpivot, then pivot on a different exis). A portable solution is to use union, and then conditional aggregation:

select 
    name,
    max(case when category = 'A' then val end) as valA,
    max(case when category = 'B' then val end) as valB
from (
    select category, premium val, 'premium' name from mytable
    union all select category, net_premium, 'net_premium' from mytable
    union all select category, claims, 'claims' from mytable
    union all select category, fees, 'fees' from mytable
    union all select category, expenses, 'expenses' from mytable
    union all select category, commissions, 'commissions' from mytable
) x
group by name

Important: for union to work, the datatypes of all columns being unioned must be the same (it seems like it is the case with your data, that as decimal values everywhere). If not, you need to do conversions in order to align the datatypes.

How to I Pivot this table?

1 Answers1