Transpose partially using SQL

Question

I would like to do a transposition to create columns from long list.

Here is the example

+-------+--------+--------+-------+
|  id   | typeid |  type  | value |
+-------+--------+--------+-------+
| a0001 |     01 | sales  |    10 |
| a0001 |     02 | revune |     3 |
| a0001 |     03 | asset  |     6 |
| a0002 |     01 | sales  |     8 |
| a0002 |     03 | asset  |     2 |
| a0003 |     01 | sales  |    12 |
| a0003 |     02 | revune |     8 |
| a0003 |     03 | asset  |     8 |
+-------+--------+--------+-------+

Since the value in type is enumerable, I would like to transform it into separate columns.

Here is the one I expected:

+-------+-------+---------+-------+
|  id   | sales | revenue | asset |
+-------+-------+---------+-------+
| a0001 |    10 | 3       |     6 |
| a0002 |     8 | null    |     2 |
| a0003 |    12 | 8       |     8 |
+-------+-------+---------+-------+

I know how to do it in py/js.

I would like to know if it is possible to transpose using SQL in the database query?

[See this link](https://stackoverflow.com/q/58480825/11882693) — Arif Sher Khan, Oct 22 '19 at 11:33

score 0 · Accepted Answer · answered Oct 22 '19 at 10:40

0

If you know exactly which columns you want, you can use conditional aggregation:

select id,
       sum(case when type = 'sales' then value end) as sales,
       sum(case when type = 'revenue' then value end) as revenue,
       sum(case when type = 'assets' then value end) as assets
from t
group by id;

If you want this to be flexible, then you need to construct the SQL as a string and execute it. That is called dynamic SQL and depends very much on the database you are using.

answered Oct 22 '19 at 10:40

Gordon Linoff

1,242,037
58
646
786

Thanks, but if the number of the value is very large, eg type = {x1,x2, .... x1000}. is there a way to write it in one go instead of using case for each value? – Leoli Oct 22 '19 at 10:44
@Leoli . . . You can generate the code using a SQL query or spreadsheet, using the `INFORMATION_SCHEMA` views. – Gordon Linoff Oct 22 '19 at 13:42

score 0 · Answer 2 · answered Oct 22 '19 at 10:41

You could use conditional aggregation

select a.id
    , sum(case when typeid='01' then value else 0 end) sales
    , sum(case when typeid='02' then value else 0 end) revenue
    , sum(case when typeid='03' then value else 0 end) asset
from my_table
group by id

Transpose partially using SQL

2 Answers2