Question: I have the table like below where i want to insert similar row for all columns except contact type where every numeric column like(permission_volume, volume_last_month etc) will be 0 and contact type will have different values, so I have contact_type like (Digital, No_Permission, Email, Post, SMS, Phone) and I want to insert all other contact types which is not available in the contact_type column
+----------------+-------+---------------+-------------------+--------------+-------------------+------------+------------------+----------------+-------------------+--------------------------+---------------------------+-----------------------+
| market | brand | contact_type | permission_volume | custom_group | consent_type | period | calculation_type | region | volume_last_month | avg_last_3_months_volume | this_month_last_yr_volume | total_customer_volume |
+----------------+-------+---------------+-------------------+--------------+-------------------+------------+------------------+----------------+-------------------+--------------------------+---------------------------+-----------------------+
| United Kingdom | LR | Post | 1 | Lapsed Leads | Owner and Vehicle | 2022-08-01 | cumulative | United Kingdom | 1 | 2 | 2 | 3 |
| United Kingdom | LR | No_Permission | 10028 | Lapsed Leads | Owner and Vehicle | 2022-08-01 | cumulative | United Kingdom | 12888 | 11641 | 4 | 5 |
| United Kingdom | JG | Digital | 1 | Active Owner | PR | 2022-08-01 | cumulative | United Kingdom | 1 | 2 | 2 | 3 |
+----------------+-------+---------------+-------------------+--------------+-------------------+------------+------------------+----------------+-------------------+--------------------------+---------------------------+-----------------------+
Expected Output:
+----------------+-------+---------------+-------------------+--------------+-------------------+------------+------------------+----------------+-------------------+--------------------------+---------------------------+-----------------------+
| market | brand | contact_type | permission_volume | custom_group | consent_type | period | calculation_type | region | volume_last_month | avg_last_3_months_volume | this_month_last_yr_volume | total_customer_volume |
+----------------+-------+---------------+-------------------+--------------+-------------------+------------+------------------+----------------+-------------------+--------------------------+---------------------------+-----------------------+
| United Kingdom | LR | Post | 1 | Lapsed Leads | Owner and Vehicle | 2022-08-01 | cumulative | United Kingdom | 1 | 2 | 2 | 3 |
| United Kingdom | LR | No_Permission | 10028 | Lapsed Leads | Owner and Vehicle | 2022-08-01 | cumulative | United Kingdom | 12888 | 11641 | 4 | 5 |
| United Kingdom | LR | Digital | 0 | Lapsed Leads | Owner and Vehicle | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
| United Kingdom | LR | Email | 0 | Lapsed Leads | Owner and Vehicle | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
| United Kingdom | LR | SMS | 0 | Lapsed Leads | Owner and Vehicle | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
| United Kingdom | LR | Phone | 0 | Lapsed Leads | Owner and Vehicle | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
| United Kingdom | JG | Digital | 1 | Active Owner | PR | 2022-08-01 | cumulative | United Kingdom | 1 | 2 | 2 | 3 |
| United Kingdom | JG | Phone | 0 | Active Owner | PR | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
| United Kingdom | JG | Email | 0 | Active Owner | PR | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
| United Kingdom | JG | SMS | 0 | Active Owner | PR | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
| United Kingdom | JG | No_Permission | 0 | Active Owner | PR | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
| United Kingdom | JG | Post | 0 | Active Owner | PR | 2022-08-01 | cumulative | United Kingdom | 0 | 0 | 0 | 0 |
+----------------+-------+---------------+-------------------+--------------+-------------------+------------+------------------+----------------+-------------------+--------------------------+---------------------------+-----------------------+
Tried with this but it's not working:
declare i int64 default 1;
declare count_of_markets int64;
execute immediate "select count(distinct market) from `table1`" into count_of_markets;
CREATE OR REPLACE TABLE `lookup_table` (
id int64 not null, contact_type STRING NOT NULL, brand string NOT NULL
);
while i <= count_of_markets do
INSERT INTO `lookup_table`
(id, contact_type,brand)
VALUES
(i,"Email","JG"),
(i,"SMS","JG"),
(i,"Phone","JG"),
(i,"Digital","JG"),
(i,"No_Permission","JG"),
(i,"Post","JG"),
(i,"Email","LR"),
(i,"SMS","LR"),
(i,"Phone","LR"),
(i,"Digital","LR"),
(i,"No_Permission","LR"),
(i,"Post","LR");
set i = i+1;
end while;
create or replace table `table1`
partition by period as
select * from(
with cte_market as (select
*
, ROW_NUMBER() OVER(ORDER BY market) as id
from (
select distinct market from
`table1`)),
contact_CTE as (select * from `lookup_table`),
step3 as (select cte_market.market, contact_CTE.brand,contact_CTE.contact_type from cte_market left join contact_CTE
on cte_market.id=contact_CTE.id)
select a.market as market, a.brand as brand, a.contact_type as contact_type, a.permission_volume as permission_volume, a.custom_group as custom_group, a.consent_type as consent_type, a.period as period, a.calculation_type as calculation_type, a.region as region, a.volume_last_month as volume_last_month, a.avg_last_3_months_volume as avg_last_3_months_volume, a.this_month_last_yr_volume as this_month_last_yr_volume, a.total_customer_volume as total_customer_volume
from `table1` a full outer join step3
on a.market=step3.market
and a.brand=step3.brand
and a.contact_type=step3.contact_type)