How to translate this Excel formulas to SQL?

Question

I have an Excel table(not formatted as an Excel table Object) that looks like this: And I need to translate this to a SQL code.

So far I managed to make it like this:

Sum( CASE WHEN CONCAT( table.product_code, "_", table.account_id,
      table.account_name) = table2.account 
THEN Value ELSE 0 END)
AS 'Cost'

But, as you can see the parameter (the product code) is not the same as indicated by the yellow row, so how do I take this moving parameter into account?

Thanks!

Answer 1

That is possibly with static columns.

create local temp table temp_table (
                                     product_code int         not null,
                                     account_id   int         not null,
                                     account_name varchar(25) not null,
                                     value        int         not null
                                   )
;


insert into temp_table
values (3001, 1005, 'Loading Fee', 50000),
       (3001, 1006, 'Seling Fee',  35000),
       (3002, 1007, 'Port-Charge', 12400),
       (3003, 1006, 'Seling Fee',  23000),
       (3005, 1007, 'Port-Charge', 18600),
       (3001, 1006, 'Seling Fee',  12000)
;


select account_id,
       account_name,
       sum(case when product_code = 3001
                then value
                else 0
            end)                            as "3001",
       sum(case when product_code = 3002
                then value
                else 0
            end)                            as "3002",
       sum(case when product_code = 3003
                then value
                else 0
            end)                            as "3003",
       sum(case when product_code = 3004
                then value
                else 0
            end)                            as "3004",
       sum(case when product_code = 3005
                then value
                else 0
            end)                            as "3005"
  from temp_table
 group by account_id,
          account_name
 order by account_id
;

Result:

 account_id     account_name     3001     3002     3003     3004     3005    
 -------------  ---------------  -------  -------  -------  -------  ------- 
 1005           Loading Fee      50000    0        0        0        0       
 1006           Seling Fee       47000    0        23000    0        0       
 1007           Port-Charge      0        12400    0        0        18600   

If you want build it with dynamic columns you could try use dynamic write sql.

create temporary table temp_table (
                                     product_code int         not null,
                                     account_id   int         not null,
                                     account_name varchar(25) not null,
                                     value        int         not null
                                   )
;


insert into temp_table
values (3001, 1005, 'Loading Fee', 50000),
       (3001, 1006, 'Seling Fee',  35000),
       (3002, 1007, 'Port-Charge', 12400),
       (3003, 1006, 'Seling Fee',  23000),
       (3005, 1007, 'Port-Charge', 18600),
       (3001, 1006, 'Seling Fee',  12000),
       (3008, 1007, 'Port-Charge', 18600),
       (3009, 1006, 'Seling Fee',  12000),
       (3011, 1006, 'Seling Fee',  12000),
       (3018, 1007, 'Port-Charge', 18600),
       (3019, 1006, 'Seling Fee',  12000)
;

select group_concat(distinct concat('ifnull(sum(case when product_code = ''', product_code, ''' then value end), 0) as `', product_code, '`'))
  into @sql
  from temp_table
;
set @sql = concat('select account_id,
                          account_name, ', @sql, '
                     from temp_table 
                     group by account_id,
                              account_name');




prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;