--- March 2022

----------------------------------------------------------------------------------------------------------------------------

-- This SQL program produces the ENV_tech table, which lists the environment-related (ENV) technology domains for patents.  
-- The technology domains have a 4-layers hierarchy (from larger to smaller domains). There is a column for the CPC/IPC codes
-- and one column for each domain hierarchy level. This table is then used to run the program that produces the ENV patent 
-- plus other tables (adaptation and ocean). 

-- Notice that the same script has to be repeated for CPC and IPC codes, the two tables merged and duplicates dropped
-- Here are presented only the CPC codes searches (the same search is done for IPC codes)

----------------------------------------------------------------------------------------------------------------------------
------------------------------------ 1. Environmental management ------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
----- 1.1.  Air pollution abatement
----------------------------------------------------------------------------------------------------------------------------

---------- 1.1.1. Emissions abatement from stationary sources (e.g. SOx, NOx, PM emissions from combustion plants)

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/34%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/38%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/4%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/5%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/6%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/7%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/8%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/9%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F23G   7/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F23J  15%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F27B   1/18%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C21B   7/22%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C21C   5/38%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F23B  80%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F23C   9%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F23C  10%'

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Environmental management'
update dbo.t1 set Sub_domain1='Air pollution abatement'
update dbo.t1 set Sub_domain2='Emissions abatement from stationary sources (e.g. SOx, NOx, PM emissions from combustion plants)'
update dbo.t1 set Sub_domain3=NULL


------ 1.1.2. Emissions abatement from mobile sources (e.g. NOx, CO, HC, PM emissions from motor vehicles)

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  53/9%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01J  23/38'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01J  23/40'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01J  23/42'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01J  23/44'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01J  23/46%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01M  13%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01M2013%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02B  47/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02B  47/10%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02D  21/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02D  21/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02D  21/10%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  26%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M2026%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'G01M  15/10%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02B  47/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02D  41%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02D  43%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02D  45%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M   3/02%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M   3/04%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M   3/05%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  23%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  25%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  27%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  31/02%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  31/04%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  31/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  31/07%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  31/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  31/09%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  31/1%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  39%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  41%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  43%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  45%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  47%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  49%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  51%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  53%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  55%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  57%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  59%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  61%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  63%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  65%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  69%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  71%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02M  67%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F02P   5%'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Environmental management'
update dbo.t2 set Sub_domain1='Air pollution abatement'
update dbo.t2 set Sub_domain2='Emissions abatement from mobile sources (e.g. NOx, CO, HC, PM emissions from motor vehicles)'
update dbo.t2 set Sub_domain3=NULL


---------- 1.1.3. Air pollution abatement - Not elsewhere classified 

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  46%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  47%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  49%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  50%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B01D  51%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B03C   3%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01N   3%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01N   5%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01N  13/00%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01N   9%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01N  11%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C10L  10/02%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C10L  10/06%'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Environmental management'
update dbo.t3 set Sub_domain1='Air pollution abatement'
update dbo.t3 set Sub_domain2='Air pollution abatement - Not elsewhere classified'
update dbo.t3 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 1.2.  Water pollution abatement
----------------------------------------------------------------------------------------------------------------------------

---------- 1.2.1. Water and wastewater treatment

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63J   4%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C02F%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C09K   3/32%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03C   1/12%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03F%'

alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Environmental management'
update dbo.t4 set Sub_domain1='Water pollution abatement'
update dbo.t4 set Sub_domain2='Water and wastewater treatment'
update dbo.t4 set Sub_domain3=NULL

---------- 1.2.2. Fertilizers from wastewater

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C05F   7%'

alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Environmental management'
update dbo.t5 set Sub_domain1='Water pollution abatement'
update dbo.t5 set Sub_domain2='Fertilizers from wastewater'
update dbo.t5 set Sub_domain3=NULL

---------- 1.2.3. Oil spill and pollutant clean-up

drop table dbo.t6
select distinct CPC_class_symbol as 'Code'
into dbo.t6
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B  15/04%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B  15/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B  15/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B  15/10%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B2015/005'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B  35/32%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C09K   3/32%'

alter table dbo.t6 add 	Domain VARCHAR(300)
alter table dbo.t6 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain3	VARCHAR(300)

update dbo.t6 set Domain='Environmental management'
update dbo.t6 set Sub_domain1='Water pollution abatement'
update dbo.t6 set Sub_domain2='Oil spill and pollutant clean-up'
update dbo.t6 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 1.3.  Waste management
----------------------------------------------------------------------------------------------------------------------------

---------- 1.3.1. Solid waste collection

drop table dbo.t7
select distinct CPC_class_symbol as 'Code'
into dbo.t7
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E01H  15%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B65F%'

alter table dbo.t7 add 	Domain VARCHAR(300)
alter table dbo.t7 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain3	VARCHAR(300)

update dbo.t7 set Domain='Environmental management'
update dbo.t7 set Sub_domain1='Waste management'
update dbo.t7 set Sub_domain2='Solid waste collection'
update dbo.t7 set Sub_domain3=NULL

---------- 1.3.2. Material recovery, recycling and re-use

drop table dbo.t8
select distinct CPC_class_symbol as 'Code'
into dbo.t8
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A23K  10/26%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A23K  10/28%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A23K  10/32%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A23K  10/33%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A23K  10/37%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A23K  10/38%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A43B   1/12%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B03B   9/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B22F   8%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B29B   7/66%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B29B  17%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B30B   9/32%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B62D  67%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B65H  73%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B65D  65/46%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C03B   1/02%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B   7/24%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B   7/26%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B   7/28%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B   7/30%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B  11/26%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B  18/04%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B  18/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B  18/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B  18/1%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B  18/2%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B  18/3%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C04B  33/132%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C08J  11%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C09K  11/01%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C10M 175%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C22B   7%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C22B  19/28%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C22B  19/30%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C22B  25/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'D01G  11%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'D21B   1/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'D21B   1/10%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'D21B   1/32%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'D21C   5/02%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'D21H  17/01%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'H01B  15/00%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'H01J   9/52%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'H01M   6/52%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'H01M  10/54%'

alter table dbo.t8 add 	Domain VARCHAR(300)
alter table dbo.t8 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain3	VARCHAR(300)

update dbo.t8 set Domain='Environmental management'
update dbo.t8 set Sub_domain1='Waste management'
update dbo.t8 set Sub_domain2='Material recovery, recycling and re-use'
update dbo.t8 set Sub_domain3=NULL

---------- 1.3.3. Fertilizers from waste

drop table dbo.t9
select distinct CPC_class_symbol as 'Code'
into dbo.t9
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C05F%'

alter table dbo.t9 add 	Domain VARCHAR(300)
alter table dbo.t9 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain3	VARCHAR(300)

update dbo.t9 set Domain='Environmental management'
update dbo.t9 set Sub_domain1='Waste management'
update dbo.t9 set Sub_domain2='Fertilizers from waste'
update dbo.t9 set Sub_domain3=NULL

---------- 1.3.4. Incineration and energy recovery

drop table dbo.t10
select distinct CPC_class_symbol as 'Code'
into dbo.t10
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C10L   5/46%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C10L   5/48%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F23G   5%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F23G   7%'

alter table dbo.t10 add 	Domain VARCHAR(300)
alter table dbo.t10 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain3	VARCHAR(300)

update dbo.t10 set Domain='Environmental management'
update dbo.t10 set Sub_domain1='Waste management'
update dbo.t10 set Sub_domain2='Incineration and energy recovery'
update dbo.t10 set Sub_domain3=NULL

---------- 1.3.6. Waste management – Not elsewhere classified

drop table dbo.t11
select distinct CPC_class_symbol as 'Code'
into dbo.t11
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B09B%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C10G   1/10%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A61L  11%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B02C  19/0075%'

alter table dbo.t11 add 	Domain VARCHAR(300)
alter table dbo.t11 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain3	VARCHAR(300)

update dbo.t11 set Domain='Environmental management'
update dbo.t11 set Sub_domain1='Waste management'
update dbo.t11 set Sub_domain2='Waste management – Not elsewhere classified'
update dbo.t11 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 1.4.  Soil remediation
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t12
select distinct CPC_class_symbol as 'Code'
into dbo.t12
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B09C%'

alter table dbo.t12 add 	Domain VARCHAR(300)
alter table dbo.t12 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain3	VARCHAR(300)

update dbo.t12 set Domain='Environmental management'
update dbo.t12 set Sub_domain1='Soil remediation'
update dbo.t12 set Sub_domain2=NULL
update dbo.t12 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 1.5.  Environmental monitoring
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t13
select distinct CPC_class_symbol as 'Code'
into dbo.t13
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01N  11%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'G08B  21/12%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'G08B  21/14%'

alter table dbo.t13 add 	Domain VARCHAR(300)
alter table dbo.t13 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain3	VARCHAR(300)

update dbo.t13 set Domain='Environmental management'
update dbo.t13 set Sub_domain1='Environmental monitoring'
update dbo.t13 set Sub_domain2=NULL
update dbo.t13 set Sub_domain3=NULL

drop table dbo.management
select * 
into dbo.management
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=13)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.management'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO

----------------------------------------------------------------------------------------------------------------------------
------------ 2. Climate change mitigation technologies related to energy generation, transmission or distribution --------
----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
----- 2. Climate change mitigation technologies related to energy generation, transmission or distribution
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t25
select distinct CPC_class_symbol as 'Code'
into dbo.t25
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E%'

alter table dbo.t25 add 	Domain VARCHAR(300)
alter table dbo.t25 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t25 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t25 add 	Sub_domain3	VARCHAR(300)

update dbo.t25 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t25 set Sub_domain1=NULL
update dbo.t25 set Sub_domain2=NULL
update dbo.t25 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 2.1. Renewable energy generation
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t26
select distinct CPC_class_symbol as 'Code'
into dbo.t26
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10%'

alter table dbo.t26 add 	Domain VARCHAR(300)
alter table dbo.t26 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t26 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t26 add 	Sub_domain3	VARCHAR(300)

update dbo.t26 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t26 set Sub_domain1='Renewable energy generation'
update dbo.t26 set Sub_domain2=NULL
update dbo.t26 set Sub_domain3=NULL

---------- 2.1.1. Wind energy 

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/7%'

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t1 set Sub_domain1='Renewable energy generation'
update dbo.t1 set Sub_domain2='Wind energy'
update dbo.t1 set Sub_domain3=NULL


---------- 2.1.2. Solar thermal energy

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/4%'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t2 set Sub_domain1='Renewable energy generation'
update dbo.t2 set Sub_domain2='Solar thermal energy'
update dbo.t2 set Sub_domain3=NULL


---------- 2.1.3. Solar photovoltaic (PV) energy

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/5%'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t3 set Sub_domain1='Renewable energy generation'
update dbo.t3 set Sub_domain2='Solar photovoltaic (PV) energy'
update dbo.t3 set Sub_domain3=NULL


---------- 2.1.4. Solar thermal-PV hybrids

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/60%'

alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t4 set Sub_domain1='Renewable energy generation'
update dbo.t4 set Sub_domain2='Solar thermal-PV hybrids'
update dbo.t4 set Sub_domain3=NULL


---------- 2.1.5. Geothermal energy

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/10'

alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t5 set Sub_domain1='Renewable energy generation'
update dbo.t5 set Sub_domain2='Geothermal energy'
update dbo.t5 set Sub_domain3=NULL


---------- 2.1.6. Marine energy, e.g. using wave energy or salinity gradient

drop table dbo.t6
select distinct CPC_class_symbol as 'Code'
into dbo.t6
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/30'

alter table dbo.t6 add 	Domain VARCHAR(300)
alter table dbo.t6 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain3	VARCHAR(300)

update dbo.t6 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t6 set Sub_domain1='Renewable energy generation'
update dbo.t6 set Sub_domain2='Marine energy, e.g. using wave energy or salinity gradient'
update dbo.t6 set Sub_domain3=NULL


---------- 2.1.7. Hydro energy

drop table dbo.t7
select distinct CPC_class_symbol as 'Code'
into dbo.t7
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/20'

alter table dbo.t7 add 	Domain VARCHAR(300)
alter table dbo.t7 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain3	VARCHAR(300)

update dbo.t7 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t7 set Sub_domain1='Renewable energy generation'
update dbo.t7 set Sub_domain2='Hydro energy'
update dbo.t7 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 2.2.  Energy generation from fuels of non-fossil origin
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t27
select distinct CPC_class_symbol as 'Code'
into dbo.t27
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  50%'

alter table dbo.t27 add 	Domain VARCHAR(300)
alter table dbo.t27 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t27 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t27 add 	Sub_domain3	VARCHAR(300)

update dbo.t27 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t27 set Sub_domain1='Energy generation from fuels of non-fossil origin'
update dbo.t27 set Sub_domain2=NULL
update dbo.t27 set Sub_domain3=NULL

---------- 2.2.1. Biofuels, e.g. bio-diesel

drop table dbo.t8
select distinct CPC_class_symbol as 'Code'
into dbo.t8
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  50/10'

alter table dbo.t8 add 	Domain VARCHAR(300)
alter table dbo.t8 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain3	VARCHAR(300)

update dbo.t8 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t8 set Sub_domain1='Energy generation from fuels of non-fossil origin'
update dbo.t8 set Sub_domain2='Biofuels, e.g. bio-diesel'
update dbo.t8 set Sub_domain3=NULL


---------- 2.2.2. Fuel from waste, e.g. synthetic alcohol or diesel

drop table dbo.t9
select distinct CPC_class_symbol as 'Code'
into dbo.t9
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  50/30'

alter table dbo.t9 add 	Domain VARCHAR(300)
alter table dbo.t9 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain3	VARCHAR(300)

update dbo.t9 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t9 set Sub_domain1='Energy generation from fuels of non-fossil origin'
update dbo.t9 set Sub_domain2='Fuel from waste, e.g. synthetic alcohol or diesel'
update dbo.t9 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 2.3. Combustion technologies with mitigation potential (e.g. using fossil fuels, biomass, waste, etc.)
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t28
select distinct CPC_class_symbol as 'Code'
into dbo.t28
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  20%'

alter table dbo.t28 add 	Domain VARCHAR(300)
alter table dbo.t28 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t28 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t28 add 	Sub_domain3	VARCHAR(300)

update dbo.t28 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t28 set Sub_domain1='Combustion technologies with mitigation potential (e.g. using fossil fuels, biomass, waste, etc.)'
update dbo.t28 set Sub_domain2=NULL
update dbo.t28 set Sub_domain3=NULL

---------- 2.3.1. Technologies for improved output efficiency (combined heat and power, combined cycles, etc.)

drop table dbo.t10
select distinct CPC_class_symbol as 'Code'
into dbo.t10
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  20/1%'

alter table dbo.t10 add 	Domain VARCHAR(300)
alter table dbo.t10 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain3	VARCHAR(300)

update dbo.t10 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t10 set Sub_domain1='Combustion technologies with mitigation potential (e.g. using fossil fuels, biomass, waste, etc.)'
update dbo.t10 set Sub_domain2='Technologies for improved output efficiency (combined heat and power, combined cycles, etc.)'
update dbo.t10 set Sub_domain3=NULL


---------- 2.3.2. Technologies for improved input efficiency (efficient combustion or heat usage)

drop table dbo.t11
select distinct CPC_class_symbol as 'Code'
into dbo.t11
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  20/3%'

alter table dbo.t11 add 	Domain VARCHAR(300)
alter table dbo.t11 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain3	VARCHAR(300)

update dbo.t11 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t11 set Sub_domain1='Combustion technologies with mitigation potential (e.g. using fossil fuels, biomass, waste, etc.)'
update dbo.t11 set Sub_domain2='Technologies for improved input efficiency (efficient combustion or heat usage)'
update dbo.t11 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 2.4. Nuclear energy
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t29
select distinct CPC_class_symbol as 'Code'
into dbo.t29
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  30%'

alter table dbo.t29 add 	Domain VARCHAR(300)
alter table dbo.t29 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t29 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t29 add 	Sub_domain3	VARCHAR(300)

update dbo.t29 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t29 set Sub_domain1='Nuclear energy'
update dbo.t29 set Sub_domain2=NULL
update dbo.t29 set Sub_domain3=NULL


---------- 2.4.1. Nuclear fusion reactors

drop table dbo.t12
select distinct CPC_class_symbol as 'Code'
into dbo.t12
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  30/10'

alter table dbo.t12 add 	Domain VARCHAR(300)
alter table dbo.t12 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain3	VARCHAR(300)

update dbo.t12 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t12 set Sub_domain1='Nuclear energy'
update dbo.t12 set Sub_domain2='Nuclear fusion reactors'
update dbo.t12 set Sub_domain3=NULL


---------- 2.4.2. Nuclear fission reactors

drop table dbo.t13
select distinct CPC_class_symbol as 'Code'
into dbo.t13
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  30/30'

alter table dbo.t13 add 	Domain VARCHAR(300)
alter table dbo.t13 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain3	VARCHAR(300)

update dbo.t13 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t13 set Sub_domain1='Nuclear energy'
update dbo.t13 set Sub_domain2='Nuclear fission reactors'
update dbo.t13 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 2.5. Technologies for an efficient electrical power generation, transmission or distribution
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t30
select distinct CPC_class_symbol as 'Code'
into dbo.t30
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  40%'

alter table dbo.t30 add 	Domain VARCHAR(300)
alter table dbo.t30 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t30 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t30 add 	Sub_domain3	VARCHAR(300)

update dbo.t30 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t30 set Sub_domain1='Technologies for an efficient electrical power generation, transmission or distribution'
update dbo.t30 set Sub_domain2=NULL
update dbo.t30 set Sub_domain3=NULL

---------- 2.5.1. Superconducting electric elements or equipment

drop table dbo.t14
select distinct CPC_class_symbol as 'Code'
into dbo.t14
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  40/60'

alter table dbo.t14 add 	Domain VARCHAR(300)
alter table dbo.t14 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t14 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t14 add 	Sub_domain3	VARCHAR(300)

update dbo.t14 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t14 set Sub_domain1='Technologies for an efficient electrical power generation, transmission or distribution'
update dbo.t14 set Sub_domain2='Superconducting electric elements or equipment'
update dbo.t14 set Sub_domain3=NULL

---------- 2.5.2. Smart grids as climate change mitigation technology in the energy generation sector

drop table dbo.t15
select distinct CPC_class_symbol as 'Code'
into dbo.t15
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  40/70'

alter table dbo.t15 add 	Domain VARCHAR(300)
alter table dbo.t15 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t15 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t15 add 	Sub_domain3	VARCHAR(300)

update dbo.t15 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t15 set Sub_domain1='Technologies for an efficient electrical power generation, transmission or distribution'
update dbo.t15 set Sub_domain2='Smart grids as climate change mitigation technology in the energy generation sector'
update dbo.t15 set Sub_domain3=NULL


---------- 2.5.3. Not elsewhere classified

drop table dbo.t16
select distinct CPC_class_symbol as 'Code'
into dbo.t16
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  40/10'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  40/20'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  40/30'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  40/40'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  40/50'

alter table dbo.t16 add 	Domain VARCHAR(300)
alter table dbo.t16 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t16 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t16 add 	Sub_domain3	VARCHAR(300)

update dbo.t16 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t16 set Sub_domain1='Technologies for an efficient electrical power generation, transmission or distribution'
update dbo.t16 set Sub_domain2='Not elsewhere classified'
update dbo.t16 set Sub_domain3=NULL



----------------------------------------------------------------------------------------------------------------------------
----- 2.6. Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t31
select distinct CPC_class_symbol as 'Code'
into dbo.t31
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60%'

alter table dbo.t31 add 	Domain VARCHAR(300)
alter table dbo.t31 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t31 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t31 add 	Sub_domain3	VARCHAR(300)

update dbo.t31 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t31 set Sub_domain1='Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)'
update dbo.t31 set Sub_domain2=NULL
update dbo.t31 set Sub_domain3=NULL


---------- 2.6.1. Energy storage

------- 2.6.1.1. Batteries

drop table dbo.t17
select distinct CPC_class_symbol as 'Code'
into dbo.t17
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/10'

alter table dbo.t17 add 	Domain VARCHAR(300)
alter table dbo.t17 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t17 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t17 add 	Sub_domain3	VARCHAR(300)

update dbo.t17 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t17 set Sub_domain1='Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)'
update dbo.t17 set Sub_domain2='Energy storage'
update dbo.t17 set Sub_domain3='Batteries'


------- 2.6.1.2. Capacitors

drop table dbo.t18
select distinct CPC_class_symbol as 'Code'
into dbo.t18
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/13'

alter table dbo.t18 add 	Domain VARCHAR(300)
alter table dbo.t18 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t18 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t18 add 	Sub_domain3	VARCHAR(300)

update dbo.t18 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t18 set Sub_domain1='Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)'
update dbo.t18 set Sub_domain2='Energy storage'
update dbo.t18 set Sub_domain3='Capacitors'


------- 2.6.1.3. Thermal energy storage

drop table dbo.t19
select distinct CPC_class_symbol as 'Code'
into dbo.t19
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/14'

alter table dbo.t19 add 	Domain VARCHAR(300)
alter table dbo.t19 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t19 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t19 add 	Sub_domain3	VARCHAR(300)

update dbo.t19 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t19 set Sub_domain1='Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)'
update dbo.t19 set Sub_domain2='Energy storage'
update dbo.t19 set Sub_domain3='Thermal energy storage'


------- 2.6.1.4. Mechanical energy storage, e.g. flywheels or pressurised fluids

drop table dbo.t20
select distinct CPC_class_symbol as 'Code'
into dbo.t20
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/16'

alter table dbo.t20 add 	Domain VARCHAR(300)
alter table dbo.t20 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t20 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t20 add 	Sub_domain3	VARCHAR(300)

update dbo.t20 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t20 set Sub_domain1='Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)'
update dbo.t20 set Sub_domain2='Energy storage'
update dbo.t20 set Sub_domain3='Mechanical energy storage, e.g. flywheels or pressurised fluids'


---------- 2.6.2. Hydrogen technology

drop table dbo.t21
select distinct CPC_class_symbol as 'Code'
into dbo.t21
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/30'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/32'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/34'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/36'

alter table dbo.t21 add 	Domain VARCHAR(300)
alter table dbo.t21 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t21 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t21 add 	Sub_domain3	VARCHAR(300)

update dbo.t21 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t21 set Sub_domain1='Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)'
update dbo.t21 set Sub_domain2='Hydrogen technology'
update dbo.t21 set Sub_domain3=NULL


---------- 2.6.3. Fuel cells

drop table dbo.t22
select distinct CPC_class_symbol as 'Code'
into dbo.t22
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/50'

alter table dbo.t22 add 	Domain VARCHAR(300)
alter table dbo.t22 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t22 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t22 add 	Sub_domain3	VARCHAR(300)

update dbo.t22 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t22 set Sub_domain1='Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)'
update dbo.t22 set Sub_domain2='Fuel cells'
update dbo.t22 set Sub_domain3=NULL


---------- 2.6.4. High-voltage direct current transmission

drop table dbo.t23
select distinct CPC_class_symbol as 'Code'
into dbo.t23
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  60/60'

alter table dbo.t23 add 	Domain VARCHAR(300)
alter table dbo.t23 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t23 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t23 add 	Sub_domain3	VARCHAR(300)

update dbo.t23 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t23 set Sub_domain1='Enabling technologies (technologies with potential or indirect contribution to GHG emission mitigation)'
update dbo.t23 set Sub_domain2='High-voltage direct current transmission'
update dbo.t23 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 2.7. Other energy conversion or management systems reducing GHG emissions
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t24
select distinct CPC_class_symbol as 'Code'
into dbo.t24
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  70%'

alter table dbo.t24 add 	Domain VARCHAR(300)
alter table dbo.t24 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t24 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t24 add 	Sub_domain3	VARCHAR(300)

update dbo.t24 set Domain='Climate change mitigation technologies related to energy generation, transmission or distribution'
update dbo.t24 set Sub_domain1='Other energy conversion or management systems reducing GHG emissions'
update dbo.t24 set Sub_domain2=NULL
update dbo.t24 set Sub_domain3=NULL


drop table dbo.energy
select * 
into dbo.energy
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=31)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.energy'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO

----------------------------------------------------------------------------------------------------------------------------
----------------------- 3. Capture, storage, sequestration or disposal of greenhouse gases ---------------------------------
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02C%'

alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Capture, storage, sequestration or disposal of greenhouse gases'
update dbo.t5 set Sub_domain1=NULL
update dbo.t5 set Sub_domain2=NULL
update dbo.t5 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 3.1. CAPTURE OR DISPOSAL OF NITROUS OXIDE (N2O)
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02C  20/10'

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Capture, storage, sequestration or disposal of greenhouse gases'
update dbo.t1 set Sub_domain1='Capture or disposal of nitrous oxide (N2O)'
update dbo.t1 set Sub_domain2=NULL
update dbo.t1 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 3.2. Capture or disposal of methane (CH4)
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02C  20/20'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Capture, storage, sequestration or disposal of greenhouse gases'
update dbo.t2 set Sub_domain1='Capture or disposal of methane (CH4)'
update dbo.t2 set Sub_domain2=NULL
update dbo.t2 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 3.3. Capture or disposal of perfluorocarbons (PFC), hydrofluorocarbons (HFC) or sulfur hexafluoride (SF6)
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02C  20/30'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Capture, storage, sequestration or disposal of greenhouse gases'
update dbo.t3 set Sub_domain1='Capture or disposal of perfluorocarbons (PFC), hydrofluorocarbons (HFC) or sulfur hexafluoride (SF6)'
update dbo.t3 set Sub_domain2=NULL
update dbo.t3 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 3.4. Capture or disposal of carbon dioxide (CO2)
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02C  20/40'

alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Capture, storage, sequestration or disposal of greenhouse gases'
update dbo.t4 set Sub_domain1='Capture or disposal of carbon dioxide (CO2)'
update dbo.t4 set Sub_domain2=NULL
update dbo.t4 set Sub_domain3=NULL

drop table dbo.ghg
select * 
into dbo.ghg
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=5)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.ghg'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO

----------------------------------------------------------------------------------------------------------------------------
----------------------- 4. Climate change mitigation technologies related to transportation --------------------------------
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t10
select distinct CPC_class_symbol as 'Code'
into dbo.t10
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T%'

alter table dbo.t10 add 	Domain VARCHAR(300)
alter table dbo.t10 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain3	VARCHAR(300)

update dbo.t10 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t10 set Sub_domain1=NULL
update dbo.t10 set Sub_domain2=NULL
update dbo.t10 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 4.1. Road transport
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t11
select distinct CPC_class_symbol as 'Code'
into dbo.t11
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10%'

alter table dbo.t11 add 	Domain VARCHAR(300)
alter table dbo.t11 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain3	VARCHAR(300)

update dbo.t11 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t11 set Sub_domain1='Road transport'
update dbo.t11 set Sub_domain2=NULL
update dbo.t11 set Sub_domain3=NULL

---------- 4.1.1. Conventional vehicles (based on internal combustion engine)

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/10'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/12'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/30'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/40'

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t1 set Sub_domain1='Road transport'
update dbo.t1 set Sub_domain2='Conventional vehicles (based on internal combustion engine)'
update dbo.t1 set Sub_domain3=NULL


---------- 4.1.2. Hybrid vehicles

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/62'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t2 set Sub_domain1='Road transport'
update dbo.t2 set Sub_domain2='Hybrid vehicles'
update dbo.t2 set Sub_domain3=NULL


---------- 4.1.3. Electric vehicles

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/64'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/70'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/7072'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/72'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t3 set Sub_domain1='Road transport'
update dbo.t3 set Sub_domain2='Electric vehicles'
update dbo.t3 set Sub_domain3=NULL


---------------- 4.1.4. Fuel efficiency-improving vehicle design (common to all road vehicles)

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/8%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  10/9%'

alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t4 set Sub_domain1='Road transport'
update dbo.t4 set Sub_domain2='Fuel efficiency-improving vehicle design (common to all road vehicles)'
update dbo.t4 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 4.2. Rail transport
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  30/00'

alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t5 set Sub_domain1='Rail transport'
update dbo.t5 set Sub_domain2=NULL
update dbo.t5 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 4.3. Aeronautics or air transport
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t6
select distinct CPC_class_symbol as 'Code'
into dbo.t6
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  50%'

alter table dbo.t6 add 	Domain VARCHAR(300)
alter table dbo.t6 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain3	VARCHAR(300)

update dbo.t6 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t6 set Sub_domain1='Aeronautics or air transport'
update dbo.t6 set Sub_domain2=NULL
update dbo.t6 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 4.4. Maritime or waterways transport
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t7
select distinct CPC_class_symbol as 'Code'
into dbo.t7
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  70%'

alter table dbo.t7 add 	Domain VARCHAR(300)
alter table dbo.t7 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain3	VARCHAR(300)

update dbo.t7 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t7 set Sub_domain1='Maritime or waterways transport'
update dbo.t7 set Sub_domain2=NULL
update dbo.t7 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 4.5. Enabling technologies in transport
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t12
select distinct CPC_class_symbol as 'Code'
into dbo.t12
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  90%'

alter table dbo.t12 add 	Domain VARCHAR(300)
alter table dbo.t12 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain3	VARCHAR(300)

update dbo.t12 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t12 set Sub_domain1='Enabling technologies in transport'
update dbo.t12 set Sub_domain2=NULL
update dbo.t12 set Sub_domain3=NULL

-------- 4.5.1. Electric vehicle charging

drop table dbo.t8
select distinct CPC_class_symbol as 'Code'
into dbo.t8
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  90/1%'

alter table dbo.t8 add 	Domain VARCHAR(300)
alter table dbo.t8 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain3	VARCHAR(300)

update dbo.t8 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t8 set Sub_domain1='Enabling technologies in transport'
update dbo.t8 set Sub_domain2='Electric vehicle charging'
update dbo.t8 set Sub_domain3=NULL


-------- 4.5.2. Application of hydrogen technology to transportation, e.g. using fuel cells

drop table dbo.t9
select distinct CPC_class_symbol as 'Code'
into dbo.t9
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  90/40'

alter table dbo.t9 add 	Domain VARCHAR(300)
alter table dbo.t9 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain3	VARCHAR(300)

update dbo.t9 set Domain='Climate change mitigation technologies related to transportation'
update dbo.t9 set Sub_domain1='Enabling technologies in transport'
update dbo.t9 set Sub_domain2='Application of hydrogen technology to transportation, e.g. using fuel cells'
update dbo.t9 set Sub_domain3=NULL


drop table dbo.transport
select * 
into dbo.transport
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=12)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.transport'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO

----------------------------------------------------------------------------------------------------------------------------
----------------------- 5. Climate change mitigation technologies related to buildings -------------------------------------
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t9
select distinct CPC_class_symbol as 'Code'
into dbo.t9
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B%' 

alter table dbo.t9 add 	Domain VARCHAR(300)
alter table dbo.t9 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain3	VARCHAR(300)

update dbo.t9 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t9 set Sub_domain1=NULL
update dbo.t9 set Sub_domain2=NULL
update dbo.t9 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 5.1. Integration of renewable energy sources in buildings
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B  10%' 

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t1 set Sub_domain1='Integration of renewable energy sources in buildings'
update dbo.t1 set Sub_domain2=NULL
update dbo.t1 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 5.2. Energy efficiency in buildings
----------------------------------------------------------------------------------------------------------------------------

---------- 5.2.1. Energy efficient lighting

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B  20%'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t2 set Sub_domain1='Energy efficiency in buildings'
update dbo.t2 set Sub_domain2='Energy efficient lighting'
update dbo.t2 set Sub_domain3=NULL


---------- 5.2.2. Energy efficient heating, ventilation or air conditioning [HVAC]

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B  30%'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t3 set Sub_domain1='Energy efficiency in buildings'
update dbo.t3 set Sub_domain2='Energy efficient heating, ventilation or air conditioning [HVAC]'
update dbo.t3 set Sub_domain3=NULL


---------- 5.2.3. Energy efficiency in home appliances

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B  40%'

alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t4 set Sub_domain1='Energy efficiency in buildings'
update dbo.t4 set Sub_domain2='Energy efficiency in home appliances'
update dbo.t4 set Sub_domain3=NULL


---------- 5.2.4. Energy efficient elevators, escalators and moving walkways, e.g. energy saving or recuperation technologies

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B  50%'

alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t5 set Sub_domain1='Energy efficiency in buildings'
update dbo.t5 set Sub_domain2='Energy efficient elevators, escalators and moving walkways, e.g. energy saving or recuperation technologies'
update dbo.t5 set Sub_domain3=NULL


---------- 5.2.5. End-user side

drop table dbo.t6
select distinct CPC_class_symbol as 'Code'
into dbo.t6
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B  70%'

alter table dbo.t6 add 	Domain VARCHAR(300)
alter table dbo.t6 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain3	VARCHAR(300)

update dbo.t6 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t6 set Sub_domain1='Energy efficiency in buildings'
update dbo.t6 set Sub_domain2='End-user side'
update dbo.t6 set Sub_domain3=NULL



----------------------------------------------------------------------------------------------------------------------------
----- 5.3. Architectural or constructional elements improving the thermal performance of buildings
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t7
select distinct CPC_class_symbol as 'Code'
into dbo.t7
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B  80%'

alter table dbo.t7 add 	Domain VARCHAR(300)
alter table dbo.t7 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain3	VARCHAR(300)

update dbo.t7 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t7 set Sub_domain1='Architectural or constructional elements improving the thermal performance of buildings'
update dbo.t7 set Sub_domain2=NULL
update dbo.t7 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 5.4. Enabling technologies in buildings
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t8
select distinct CPC_class_symbol as 'Code'
into dbo.t8
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02B  90%'

alter table dbo.t8 add 	Domain VARCHAR(300)
alter table dbo.t8 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain3	VARCHAR(300)

update dbo.t8 set Domain='Climate change mitigation technologies related to buildings'
update dbo.t8 set Sub_domain1='Enabling technologies in buildings'
update dbo.t8 set Sub_domain2=NULL
update dbo.t8 set Sub_domain3=NULL


drop table dbo.buildings
select * 
into dbo.buildings
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=9)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.buildings'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---- 6. CLIMATE CHANGE MITIGATION technologies related to WASTEWATER TREATMENT OR WASTE MANAGEMENT -------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t20
select distinct CPC_class_symbol as 'Code'
into dbo.t20
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W%'
alter table dbo.t20 add 	Domain VARCHAR(300)
alter table dbo.t20 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t20 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t20 add 	Sub_domain3	VARCHAR(300)

update dbo.t20 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t20 set Sub_domain1=NULL
update dbo.t20 set Sub_domain2=NULL
update dbo.t20 set Sub_domain3=NULL

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 6.1.  Wastewater treatment
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  10%'
alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t1 set Sub_domain1='Wastewater treatment'
update dbo.t1 set Sub_domain2=NULL
update dbo.t1 set Sub_domain3=NULL

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 6.2.  SOLID WASTE MANAGEMENT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t21
select distinct CPC_class_symbol as 'Code'
into dbo.t21
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30%'
alter table dbo.t21 add 	Domain VARCHAR(300)
alter table dbo.t21 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t21 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t21 add 	Sub_domain3	VARCHAR(300)

update dbo.t21 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t21 set Sub_domain1='Solid waste management'
update dbo.t21 set Sub_domain2=NULL
update dbo.t21 set Sub_domain3=NULL

---------- 6.2.1.	Waste collection, transportation, transfer or storage

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/10'
alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t2 set Sub_domain1='Solid waste management'
update dbo.t2 set Sub_domain2='Waste collection, transportation, transfer or storage'
update dbo.t2 set Sub_domain3=NULL

---------- 6.2.2.	Waste processing or separation

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/20'
alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t3 set Sub_domain1='Solid waste management'
update dbo.t3 set Sub_domain2='Waste processing or separation'
update dbo.t3 set Sub_domain3=NULL

---------- 6.2.3.	Landfill technologies aiming to mitigate methane emissions

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/30'
alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t4 set Sub_domain1='Solid waste management'
update dbo.t4 set Sub_domain2='Landfill technologies aiming to mitigate methane emissions'
update dbo.t4 set Sub_domain3=NULL


---------- 6.2.4.	Bio-organic fraction processing; Production of fertilisers from the organic fraction of waste or refuse

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/40'
alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t5 set Sub_domain1='Solid waste management'
update dbo.t5 set Sub_domain2='Bio-organic fraction processing; Production of fertilisers from the organic fraction of waste or refuse'
update dbo.t5 set Sub_domain3=NULL


---------- 6.2.5.	Reuse, recycling or recovery technologies

---- 6.2.5.1. Mechanical processing of waste for the recovery of materials, e.g. crushing, shredding, separation or disassembly

drop table dbo.t6
select distinct CPC_class_symbol as 'Code'
into dbo.t6
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/52'
alter table dbo.t6 add 	Domain VARCHAR(300)
alter table dbo.t6 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain3	VARCHAR(300)

update dbo.t6 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t6 set Sub_domain1='Solid waste management'
update dbo.t6 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t6 set Sub_domain3='Mechanical processing of waste for the recovery of materials, e.g. crushing, shredding, separation or disassembly'

---- 6.2.5.2. Waste management of vehicles

drop table dbo.t7
select distinct CPC_class_symbol as 'Code'
into dbo.t7
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/56'
alter table dbo.t7 add 	Domain VARCHAR(300)
alter table dbo.t7 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain3	VARCHAR(300)

update dbo.t7 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t7 set Sub_domain1='Solid waste management'
update dbo.t7 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t7 set Sub_domain3='Waste management of vehicles'

---- 6.2.5.3. Construction or demolition [C&D] waste

drop table dbo.t8
select distinct CPC_class_symbol as 'Code'
into dbo.t8
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/58'
alter table dbo.t8 add 	Domain VARCHAR(300)
alter table dbo.t8 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain3	VARCHAR(300)

update dbo.t8 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t8 set Sub_domain1='Solid waste management'
update dbo.t8 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t8 set Sub_domain3='Construction or demolition [C&D] waste'

---- 6.2.5.4. Glass recycling

drop table dbo.t9
select distinct CPC_class_symbol as 'Code'
into dbo.t9
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/60%'

alter table dbo.t9 add 	Domain VARCHAR(300)
alter table dbo.t9 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain3	VARCHAR(300)

update dbo.t9 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t9 set Sub_domain1='Solid waste management'
update dbo.t9 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t9 set Sub_domain3='Glass recycling'

---- 6.2.5.5. Plastics and rubber recycling

drop table dbo.t10
select distinct CPC_class_symbol as 'Code'
into dbo.t10
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/62'

alter table dbo.t10 add 	Domain VARCHAR(300)
alter table dbo.t10 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain3	VARCHAR(300)

update dbo.t10 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t10 set Sub_domain1='Solid waste management'
update dbo.t10 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t10 set Sub_domain3='Plastics and rubber recycling'

---- 6.2.5.6. Paper recycling

drop table dbo.t11
select distinct CPC_class_symbol as 'Code'
into dbo.t11
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/64'
alter table dbo.t11 add 	Domain VARCHAR(300)
alter table dbo.t11 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain3	VARCHAR(300)

update dbo.t11 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t11 set Sub_domain1='Solid waste management'
update dbo.t11 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t11 set Sub_domain3='Paper recycling'

---- 6.2.5.7. Disintegrating fibre-containing textile articles to obtain fibres for re-use

drop table dbo.t12
select distinct CPC_class_symbol as 'Code'
into dbo.t12
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/66'

alter table dbo.t12 add 	Domain VARCHAR(300)
alter table dbo.t12 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain3	VARCHAR(300)

update dbo.t12 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t12 set Sub_domain1='Solid waste management'
update dbo.t12 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t12 set Sub_domain3='Disintegrating fibre-containing textile articles to obtain fibres for re-use'


---- 6.2.5.8. Recovery of fats, fatty oils, fatty acids or other fatty substances, e.g. lanolin or waxes

drop table dbo.t13
select distinct CPC_class_symbol as 'Code'
into dbo.t13
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/74'
alter table dbo.t13 add 	Domain VARCHAR(300)
alter table dbo.t13 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain3	VARCHAR(300)

update dbo.t13 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t13 set Sub_domain1='Solid waste management'
update dbo.t13 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t13 set Sub_domain3='Recovery of fats, fatty oils, fatty acids or other fatty substances, e.g. lanolin or waxes'


---- 6.2.5.9. Recycling of wood or furniture waste

drop table dbo.t14
select distinct CPC_class_symbol as 'Code'
into dbo.t14
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/78'

alter table dbo.t14 add 	Domain VARCHAR(300)
alter table dbo.t14 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t14 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t14 add 	Sub_domain3	VARCHAR(300)

update dbo.t14 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t14 set Sub_domain1='Solid waste management'
update dbo.t14 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t14 set Sub_domain3='Recycling of wood or furniture waste'

---- 6.2.5.10. Packaging reuse or recycling, e.g. of multilayer packaging

drop table dbo.t15
select distinct CPC_class_symbol as 'Code'
into dbo.t15
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/80'

alter table dbo.t15 add 	Domain VARCHAR(300)
alter table dbo.t15 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t15 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t15 add 	Sub_domain3	VARCHAR(300)

update dbo.t15 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t15 set Sub_domain1='Solid waste management'
update dbo.t15 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t15 set Sub_domain3='Packaging reuse or recycling, e.g. of multilayer packaging'

---- 6.2.5.11. Recycling of waste of electrical or electronic equipment (WEEE)

drop table dbo.t16
select distinct CPC_class_symbol as 'Code'
into dbo.t16
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/82'

alter table dbo.t16 add 	Domain VARCHAR(300)
alter table dbo.t16 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t16 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t16 add 	Sub_domain3	VARCHAR(300)

update dbo.t16 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t16 set Sub_domain1='Solid waste management'
update dbo.t16 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t16 set Sub_domain3='Recycling of waste of electrical or electronic equipment (WEEE)'

---- 6.2.5.12. Recycling of batteries or fuel cells

drop table dbo.t17
select distinct CPC_class_symbol as 'Code'
into dbo.t17
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/84'

alter table dbo.t17 add 	Domain VARCHAR(300)
alter table dbo.t17 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t17 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t17 add 	Sub_domain3	VARCHAR(300)

update dbo.t17 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t17 set Sub_domain1='Solid waste management'
update dbo.t17 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t17 set Sub_domain3='Recycling of batteries or fuel cells'


---- 6.2.5.13. Use of waste materials as fillers for mortars or concrete

drop table dbo.t18
select distinct CPC_class_symbol as 'Code'
into dbo.t18
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  30/91'

alter table dbo.t18 add 	Domain VARCHAR(300)
alter table dbo.t18 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t18 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t18 add 	Sub_domain3	VARCHAR(300)

update dbo.t18 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t18 set Sub_domain1='Solid waste management'
update dbo.t18 set Sub_domain2='Reuse, recycling or recovery technologies'
update dbo.t18 set Sub_domain3='Use of waste materials as fillers for mortars or concrete'


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 6.3.  ENABLING TECHNOLOGIES OR TECHNOLOGIES WITH A POTENTIAL OR INDIRECT CONTRIBUTION TO GHG EMISSIONS MITIGATION 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t19
select distinct CPC_class_symbol as 'Code'
into dbo.t19
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02W  90%'
alter table dbo.t19 add 	Domain VARCHAR(300)
alter table dbo.t19 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t19 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t19 add 	Sub_domain3	VARCHAR(300)

update dbo.t19 set Domain='Climate change mitigation technologies related to wastewater treatment or waste management'
update dbo.t19 set Sub_domain1='Enabling technologies or technologies with a potential or indirect contribution to GHG emissions mitigation'
update dbo.t19 set Sub_domain2=NULL
update dbo.t19 set Sub_domain3=NULL


drop table dbo.waterwaste
select * 
into dbo.waterwaste
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=21)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.waterwaste'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO


------------------------------------------------------------------------------------------------------------------
---- 7. CLIMATE CHANGE MITIGATION TECHNOLOGIES IN THE PRODUCTION OR PROCESSING OF GOODS  -------------------------
------------------------------------------------------------------------------------------------------------------

drop table dbo.t27
select distinct CPC_class_symbol as 'Code'
into dbo.t27
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P%'

alter table dbo.t27 add 	Domain VARCHAR(300)
alter table dbo.t27 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t27 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t27 add 	Sub_domain3	VARCHAR(300)

update dbo.t27 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t27 set Sub_domain1=NULL
update dbo.t27 set Sub_domain2=NULL
update dbo.t27 set Sub_domain3=NULL

------------------------------------------------------------------------------------------------------------------
----- 7.1.  TECHNOLOGIES RELATED TO METAL PROCESSING
------------------------------------------------------------------------------------------------------------------

drop table dbo.t28
select distinct CPC_class_symbol as 'Code'
into dbo.t28
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  10%'

alter table dbo.t28 add 	Domain VARCHAR(300)
alter table dbo.t28 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t28 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t28 add 	Sub_domain3	VARCHAR(300)

update dbo.t28 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t28 set Sub_domain1='Technologies related to metal processing'
update dbo.t28 set Sub_domain2=NULL
update dbo.t28 set Sub_domain3=NULL


---------- 7.1.1.	Reduction of greenhouse gas [GHG] emissions

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  10/1%'

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t1 set Sub_domain1='Technologies related to metal processing'
update dbo.t1 set Sub_domain2='Reduction of greenhouse gas (GHG) emissions'
update dbo.t1 set Sub_domain3=NULL


---------- 7.1.2.	Process efficiency

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  10/20'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  10/25'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  10/32'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t2 set Sub_domain1='Technologies related to metal processing'
update dbo.t2 set Sub_domain2='Process efficiency'
update dbo.t2 set Sub_domain3=NULL


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 7.2. TECHNOLOGIES RELATING TO CHEMICAL INDUSTRY
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t29
select distinct CPC_class_symbol as 'Code'
into dbo.t29
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20%'

alter table dbo.t29 add 	Domain VARCHAR(300)
alter table dbo.t29 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t29 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t29 add 	Sub_domain3	VARCHAR(300)

update dbo.t29 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t29 set Sub_domain1='Technologies relating to chemical industry'
update dbo.t29 set Sub_domain2=NULL
update dbo.t29 set Sub_domain3=NULL


---------- 7.2.1. Process efficiency in chemical industry

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/10'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/129'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/133'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t3 set Sub_domain1='Technologies relating to chemical industry'
update dbo.t3 set Sub_domain2='Process efficiency in chemical industry'
update dbo.t3 set Sub_domain3=NULL


---------- 7.2.2. Feedstock

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/141'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/143'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/145'

alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t4 set Sub_domain1='Technologies relating to chemical industry'
update dbo.t4 set Sub_domain2='Feedstock'
update dbo.t4 set Sub_domain3=NULL


---------- 7.2.3. Reduction of greenhouse gas [GHG] emissions, e.g. CO2

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/151'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/155'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/156'

alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t5 set Sub_domain1='Technologies relating to chemical industry'
update dbo.t5 set Sub_domain2='Reduction of greenhouse gas [GHG] emissions, e.g. CO2'
update dbo.t5 set Sub_domain3=NULL


---------- 7.2.4. Improvements relating to chlorine production

drop table dbo.t6
select distinct CPC_class_symbol as 'Code'
into dbo.t6
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/20'

alter table dbo.t6 add 	Domain VARCHAR(300)
alter table dbo.t6 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain3	VARCHAR(300)

update dbo.t6 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t6 set Sub_domain1='Technologies relating to chemical industry'
update dbo.t6 set Sub_domain2='Improvements relating to chlorine production'
update dbo.t6 set Sub_domain3=NULL


---------- 7.2.5. Improvements relating to adipic acid or caprolactam production

drop table dbo.t7
select distinct CPC_class_symbol as 'Code'
into dbo.t7
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/30'

alter table dbo.t7 add 	Domain VARCHAR(300)
alter table dbo.t7 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain3	VARCHAR(300)

update dbo.t7 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t7 set Sub_domain1='Technologies relating to chemical industry'
update dbo.t7 set Sub_domain2='Improvements relating to adipic acid or caprolactam production'
update dbo.t7 set Sub_domain3=NULL

---------- 7.2.6. Improvements relating to fluorochloro hydrocarbon, e.g. chlorodifluoromethane [HCFC-22] production

drop table dbo.t8
select distinct CPC_class_symbol as 'Code'
into dbo.t8
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/40'

alter table dbo.t8 add 	Domain VARCHAR(300)
alter table dbo.t8 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain3	VARCHAR(300)

update dbo.t8 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t8 set Sub_domain1='Technologies relating to chemical industry'
update dbo.t8 set Sub_domain2='Improvements relating to fluorochloro hydrocarbon, e.g. chlorodifluoromethane [HCFC-22] production'
update dbo.t8 set Sub_domain3=NULL

---------- 7.2.7. Improvements relating to the production of bulk chemicals

drop table dbo.t9
select distinct CPC_class_symbol as 'Code'
into dbo.t9
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  20/50'

alter table dbo.t9 add 	Domain VARCHAR(300)
alter table dbo.t9 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain3	VARCHAR(300)

update dbo.t9 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t9 set Sub_domain1='Technologies relating to chemical industry'
update dbo.t9 set Sub_domain2='Improvements relating to the production of bulk chemicals'
update dbo.t9 set Sub_domain3=NULL


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 7.3. TECHNOLOGIES RELATING TO OIL REFINING AND PETROCHEMICAL INDUSTRY
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t30
select distinct CPC_class_symbol as 'Code'
into dbo.t30
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  30%'

alter table dbo.t30 add 	Domain VARCHAR(300)
alter table dbo.t30 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t30 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t30 add 	Sub_domain3	VARCHAR(300)

update dbo.t30 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t30 set Sub_domain1='Technologies relating to oil refining and petrochemical industry'
update dbo.t30 set Sub_domain2=NULL
update dbo.t30 set Sub_domain3=NULL

---------- 7.3.1. Bio-feedstock

drop table dbo.t10
select distinct CPC_class_symbol as 'Code'
into dbo.t10
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  30/20'

alter table dbo.t10 add 	Domain VARCHAR(300)
alter table dbo.t10 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain3	VARCHAR(300)

update dbo.t10 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t10 set Sub_domain1='Technologies relating to oil refining and petrochemical industry'
update dbo.t10 set Sub_domain2='Bio-feedstock'
update dbo.t10 set Sub_domain3=NULL


---------- 7.3.2. Ethylene production

drop table dbo.t11
select distinct CPC_class_symbol as 'Code'
into dbo.t11
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  30/40'

alter table dbo.t11 add 	Domain VARCHAR(300)
alter table dbo.t11 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain3	VARCHAR(300)

update dbo.t11 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t11 set Sub_domain1='Technologies relating to oil refining and petrochemical industry'
update dbo.t11 set Sub_domain2='Ethylene production'
update dbo.t11 set Sub_domain3=NULL


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 7.4. TECHNOLOGIES RELATING TO THE PROCESSING OF MINERALS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t31
select distinct CPC_class_symbol as 'Code'
into dbo.t31
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  40%'

alter table dbo.t31 add 	Domain VARCHAR(300)
alter table dbo.t31 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t31 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t31 add 	Sub_domain3	VARCHAR(300)

update dbo.t31 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t31 set Sub_domain1='Technologies relating to the processing of minerals'
update dbo.t31 set Sub_domain2=NULL
update dbo.t31 set Sub_domain3=NULL

---------- 7.4.1. Production of cement

drop table dbo.t12
select distinct CPC_class_symbol as 'Code'
into dbo.t12
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  40/1%'

alter table dbo.t12 add 	Domain VARCHAR(300)
alter table dbo.t12 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain3	VARCHAR(300)

update dbo.t12 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t12 set Sub_domain1='Technologies relating to the processing of minerals'
update dbo.t12 set Sub_domain2='Production of cement'
update dbo.t12 set Sub_domain3=NULL


---------- 7.4.2. Production or processing of lime

drop table dbo.t13
select distinct CPC_class_symbol as 'Code'
into dbo.t13
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  40/4%'

alter table dbo.t13 add 	Domain VARCHAR(300)
alter table dbo.t13 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain3	VARCHAR(300)

update dbo.t13 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t13 set Sub_domain1='Technologies relating to the processing of minerals'
update dbo.t13 set Sub_domain2='Production or processing of lime'
update dbo.t13 set Sub_domain3=NULL



---------- 7.4.3. Glass production

drop table dbo.t14
select distinct CPC_class_symbol as 'Code'
into dbo.t14
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  40/5%'

alter table dbo.t14 add 	Domain VARCHAR(300)
alter table dbo.t14 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t14 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t14 add 	Sub_domain3	VARCHAR(300)

update dbo.t14 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t14 set Sub_domain1='Technologies relating to the processing of minerals'
update dbo.t14 set Sub_domain2='Glass production'
update dbo.t14 set Sub_domain3=NULL


---------- 7.4.4. Production of ceramic materials or ceramic elements

drop table dbo.t15
select distinct CPC_class_symbol as 'Code'
into dbo.t15
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  40/60'

alter table dbo.t15 add 	Domain VARCHAR(300)
alter table dbo.t15 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t15 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t15 add 	Sub_domain3	VARCHAR(300)

update dbo.t15 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t15 set Sub_domain1='Technologies relating to the processing of minerals'
update dbo.t15 set Sub_domain2='Production of ceramic materials or ceramic elements'
update dbo.t15 set Sub_domain3=NULL



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 7.5. TECHNOLOGIES RELATING TO AGRICULTURE, LIVESTOCK OR AGROALIMENTARY INDUSTRIES
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t32
select distinct CPC_class_symbol as 'Code'
into dbo.t32
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60%'

alter table dbo.t32 add 	Domain VARCHAR(300)
alter table dbo.t32 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t32 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t32 add 	Sub_domain3	VARCHAR(300)

update dbo.t32 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t32 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t32 set Sub_domain2=NULL
update dbo.t32 set Sub_domain3=NULL


---------- 7.5.1. Using renewable energies, e.g. solar water pumping

drop table dbo.t16
select distinct CPC_class_symbol as 'Code'
into dbo.t16
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/12'

alter table dbo.t16 add 	Domain VARCHAR(300)
alter table dbo.t16 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t16 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t16 add 	Sub_domain3	VARCHAR(300)

update dbo.t16 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t16 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t16 set Sub_domain2='Using renewable energies, e.g. solar water pumping'
update dbo.t16 set Sub_domain3=NULL


---------- 7.5.2. Measures for saving energy, e.g. in green houses

drop table dbo.t17
select distinct CPC_class_symbol as 'Code'
into dbo.t17
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/14'

alter table dbo.t17 add 	Domain VARCHAR(300)
alter table dbo.t17 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t17 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t17 add 	Sub_domain3	VARCHAR(300)

update dbo.t17 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t17 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t17 set Sub_domain2='Measures for saving energy, e.g. in green houses'
update dbo.t17 set Sub_domain3=NULL


---------- 7.5.3. Reduction of greenhouse gas [GHG] emissions in agriculture

drop table dbo.t18
select distinct CPC_class_symbol as 'Code'
into dbo.t18
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/20'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/21'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/22'

alter table dbo.t18 add 	Domain VARCHAR(300)
alter table dbo.t18 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t18 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t18 add 	Sub_domain3	VARCHAR(300)

update dbo.t18 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t18 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t18 set Sub_domain2='Reduction of greenhouse gas [GHG] emissions in agriculture'
update dbo.t18 set Sub_domain3=NULL


---------- 7.5.4. Land use policy measures

drop table dbo.t19
select distinct CPC_class_symbol as 'Code'
into dbo.t19
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/30'

alter table dbo.t19 add 	Domain VARCHAR(300)
alter table dbo.t19 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t19 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t19 add 	Sub_domain3	VARCHAR(300)

update dbo.t19 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t19 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t19 set Sub_domain2='Land use policy measures'
update dbo.t19 set Sub_domain3=NULL


---------- 7.5.5. Afforestation or reforestation

drop table dbo.t20
select distinct CPC_class_symbol as 'Code'
into dbo.t20
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/40'

alter table dbo.t20 add 	Domain VARCHAR(300)
alter table dbo.t20 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t20 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t20 add 	Sub_domain3	VARCHAR(300)

update dbo.t20 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t20 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t20 set Sub_domain2='Afforestation or reforestation'
update dbo.t20 set Sub_domain3=NULL


---------- 7.5.6. Livestock or poultry management

drop table dbo.t21
select distinct CPC_class_symbol as 'Code'
into dbo.t21
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/50'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/52'

alter table dbo.t21 add 	Domain VARCHAR(300)
alter table dbo.t21 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t21 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t21 add 	Sub_domain3	VARCHAR(300)

update dbo.t21 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t21 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t21 set Sub_domain2='Livestock or poultry management'
update dbo.t21 set Sub_domain3=NULL


---------- 7.5.7. Fishing; Aquaculture; Aquafarming

drop table dbo.t22
select distinct CPC_class_symbol as 'Code'
into dbo.t22
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/60'

alter table dbo.t22 add 	Domain VARCHAR(300)
alter table dbo.t22 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t22 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t22 add 	Sub_domain3	VARCHAR(300)

update dbo.t22 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t22 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t22 set Sub_domain2='Fishing; Aquaculture; Aquafarming'
update dbo.t22 set Sub_domain3=NULL


---------- 7.5.8. Food processing, e.g. use of renewable energies or variable speed drives in handling, conveying or stacking

drop table dbo.t23
select distinct CPC_class_symbol as 'Code'
into dbo.t23
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/80'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/85'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/87'

alter table dbo.t23 add 	Domain VARCHAR(300)
alter table dbo.t23 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t23 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t23 add 	Sub_domain3	VARCHAR(300)

update dbo.t23 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t23 set Sub_domain1='Technologies relating to agriculture, livestock or agroalimentary industries'
update dbo.t23 set Sub_domain2='Food processing, e.g. use of renewable energies or variable speed drives in handling, conveying or stacking'
update dbo.t23 set Sub_domain3=NULL


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 7.6. TECHNOLOGIES IN THE PRODUCTION PROCESS FOR FINAL INDUSTRIAL OR CONSUMER PRODUCTS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t24
select distinct CPC_class_symbol as 'Code'
into dbo.t24
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  70%'

alter table dbo.t24 add 	Domain VARCHAR(300)
alter table dbo.t24 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t24 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t24 add 	Sub_domain3	VARCHAR(300)

update dbo.t24 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t24 set Sub_domain1='Technologies in the production process for final industrial or consumer products'
update dbo.t24 set Sub_domain2=NULL
update dbo.t24 set Sub_domain3=NULL


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 7.7. CLIMATE CHANGE MITIGATION TECHNOLOGIES FOR SECTOR-WIDE APPLICATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t25
select distinct CPC_class_symbol as 'Code'
into dbo.t25
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  80%'

alter table dbo.t25 add 	Domain VARCHAR(300)
alter table dbo.t25 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t25 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t25 add 	Sub_domain3	VARCHAR(300)

update dbo.t25 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t25 set Sub_domain1='Climate change mitigation technologies for sector-wide applications'
update dbo.t25 set Sub_domain2=NULL
update dbo.t25 set Sub_domain3=NULL


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----- 7.8. ENABLING TECHNOLOGIES WITH A POTENTIAL CONTRIBUTION TO GHG EMISSIONS MITIGATION
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

drop table dbo.t26
select distinct CPC_class_symbol as 'Code'
into dbo.t26
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  90%'

alter table dbo.t26 add 	Domain VARCHAR(300)
alter table dbo.t26 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t26 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t26 add 	Sub_domain3	VARCHAR(300)

update dbo.t26 set Domain='Climate change mitigation technologies in the production or processing of goods'
update dbo.t26 set Sub_domain1='Enabling technologies with a potential contribution to GHG emissions mitigation'
update dbo.t26 set Sub_domain2=NULL
update dbo.t26 set Sub_domain3=NULL


drop table dbo.goods
select * 
into dbo.goods
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=32)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.goods'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO


----------------------------------------------------------------------------------------------------------------
---- 8. CLIMATE CHANGE MITIGATION IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT]  -------------------------
---------------------------------------------------------------------------------------------------------------

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02D%'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Climate change mitigation in information and communication technologies (ICT)'
update dbo.t3 set Sub_domain1=NULL
update dbo.t3 set Sub_domain2=NULL
update dbo.t3 set Sub_domain3=NULL

--------------------------------------------------------------------------------------------------------------
----- 8.1.  Energy efficient computing
--------------------------------------------------------------------------------------------------------------

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02D  10%'

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Climate change mitigation in information and communication technologies (ICT)'
update dbo.t1 set Sub_domain1='Energy efficient computing'
update dbo.t1 set Sub_domain2=NULL
update dbo.t1 set Sub_domain3=NULL


-------------------------------------------------------------------------------------------------------------
----- 8.2.  Energy efficiency in communication networks
-------------------------------------------------------------------------------------------------------------

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02D  30%'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Climate change mitigation in information and communication technologies (ICT)'
update dbo.t2 set Sub_domain1='Energy efficiency in communication networks'
update dbo.t2 set Sub_domain2=NULL
update dbo.t2 set Sub_domain3=NULL


drop table dbo.ict
select * 
into dbo.ict
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=3)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.ict'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

----------------------------------------- 9. CLIMATE CHANGE ADAPTATION --------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
----- 9.1.  Adaptation at coastal zones or river basin
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t31
select distinct CPC_class_symbol as 'Code'
into dbo.t31
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  10%'

alter table dbo.t31 add 	Domain VARCHAR(300)
alter table dbo.t31 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t31 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t31 add 	Sub_domain3	VARCHAR(300)

update dbo.t31 set Domain='Climate change adaptation technologies'
update dbo.t31 set Sub_domain1='Adaptation at coastal zones or river basin'
update dbo.t31 set Sub_domain2=NULL
update dbo.t31 set Sub_domain3=NULL

---------- 9.1.1. Hard structures, e.g. dams, dykes or breakwaters

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  10/11'

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Climate change adaptation technologies'
update dbo.t1 set Sub_domain1='Adaptation at coastal zones or river basin'
update dbo.t1 set Sub_domain2='Hard structures, e.g. dams, dykes or breakwaters'
update dbo.t1 set Sub_domain3=NULL

---------- 9.1.2. Dune restoration or creation; cliff stabilisation

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  10/23'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Climate change adaptation technologies'
update dbo.t2 set Sub_domain1='Adaptation at coastal zones or river basin'
update dbo.t2 set Sub_domain2='Dune restoration or creation; cliff stabilisation'
update dbo.t2 set Sub_domain3=NULL

---------- 9.1.3. Artificial reefs or seaweed; restoration or protection of coral reefs

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  10/26'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Climate change adaptation technologies'
update dbo.t3 set Sub_domain1='Adaptation at coastal zones or river basin'
update dbo.t3 set Sub_domain2='Artificial reefs or seaweed; restoration or protection of coral reefs'
update dbo.t3 set Sub_domain3=NULL

---------- 9.1.4. Flood prevention; flood or storm water management

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  10/30'

alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Climate change adaptation technologies'
update dbo.t4 set Sub_domain1='Adaptation at coastal zones or river basin'
update dbo.t4 set Sub_domain2='Flood prevention; flood or storm water management'
update dbo.t4 set Sub_domain3=NULL

---------- 9.1.5. Controlling, monitoring or forecasting

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  10/40'

alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Climate change adaptation technologies'
update dbo.t5 set Sub_domain1='Adaptation at coastal zones or river basin'
update dbo.t5 set Sub_domain2='Controlling, monitoring or forecasting'
update dbo.t5 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 9.2.  Water resource management
----------------------------------------------------------------------------------------------------------------------------

---------- 9.2.1. Demand-side technologies (water conservation)

------ 9.2.1.1 Indoor water conservation

drop table dbo.t6
select distinct CPC_class_symbol as 'Code'
into dbo.t6
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F16K  21/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F16K  21/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F16K  21/10%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F16K  21/12%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F16K  21/16%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F16K  21/18%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F16K  21/20%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F16L  55/07%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03C   1/084%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03D   3/12%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03D   1/14%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A47K  11/12%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A47K  11/02%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03D  13/007'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03D   5/016'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   1/041'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/146'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/148'

alter table dbo.t6 add 	Domain VARCHAR(300)
alter table dbo.t6 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain3	VARCHAR(300)

update dbo.t6 set Domain='Climate change adaptation technologies'
update dbo.t6 set Sub_domain1='Water resource management'
update dbo.t6 set Sub_domain2='Demand-side technologies (water conservation)'
update dbo.t6 set Sub_domain3='Indoor water conservation'


------ 9.2.1.2 Irrigation water conservation

drop table dbo.t7
select distinct CPC_class_symbol as 'Code'
into dbo.t7
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A01G  25/02'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A01G  25/06'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'A01G  25/16'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C12N  15/8273'

alter table dbo.t7 add 	Domain VARCHAR(300)
alter table dbo.t7 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain3	VARCHAR(300)

update dbo.t7 set Domain='Climate change adaptation technologies'
update dbo.t7 set Sub_domain1='Water resource management'
update dbo.t7 set Sub_domain2='Demand-side technologies (water conservation)'
update dbo.t7 set Sub_domain3='Irrigation water conservation'


------ 9.2.1.3 Water conservation in thermoelectric power production

drop table dbo.t8
select distinct CPC_class_symbol as 'Code'
into dbo.t8
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01D  11'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/06'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/061'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/062'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/064'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/065'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/067'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/068'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/08'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/10'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/101'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/103'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/105'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/106'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F01K  23/108'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/30'

alter table dbo.t8 add 	Domain VARCHAR(300)
alter table dbo.t8 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain3	VARCHAR(300)

update dbo.t8 set Domain='Climate change adaptation technologies'
update dbo.t8 set Sub_domain1='Water resource management'
update dbo.t8 set Sub_domain2='Demand-side technologies (water conservation)'
update dbo.t8 set Sub_domain3='Water conservation in thermoelectric power production'


---------- 9.2.2. Supply-side technologies (water availability)

------ 9.2.2.1 Water collection (rain, surface and ground-water)

drop table dbo.t9
select distinct CPC_class_symbol as 'Code'
into dbo.t9
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   5%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/06%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/1%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/20%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/22%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/24%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/26%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   9%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/04'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/30'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/36'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/02%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/03%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/28'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/32'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/34'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/38'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B   3/40'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/108'

alter table dbo.t9 add 	Domain VARCHAR(300)
alter table dbo.t9 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain3	VARCHAR(300)

update dbo.t9 set Domain='Climate change adaptation technologies'
update dbo.t9 set Sub_domain1='Water resource management'
update dbo.t9 set Sub_domain2='Supply-side technologies (water availability)'
update dbo.t9 set Sub_domain3='Water collection (rain, surface and ground-water)'


------ 9.2.2.2  Water desalination

drop table dbo.t10
select distinct CPC_class_symbol as 'Code'
into dbo.t10
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/124'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/131'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/138'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/141'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/142'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/144'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C02F   1/265'

alter table dbo.t10 add 	Domain VARCHAR(300)
alter table dbo.t10 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain3	VARCHAR(300)

update dbo.t10 set Domain='Climate change adaptation technologies'
update dbo.t10 set Sub_domain1='Water resource management'
update dbo.t10 set Sub_domain2='Supply-side technologies (water availability)'
update dbo.t10 set Sub_domain3='Water desalination'

------ 9.2.2.3  Water storage and distribution

drop table dbo.t11
select distinct CPC_class_symbol as 'Code'
into dbo.t11
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E03B  11%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/15'

alter table dbo.t11 add 	Domain VARCHAR(300)
alter table dbo.t11 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t11 add 	Sub_domain3	VARCHAR(300)

update dbo.t11 set Domain='Climate change adaptation technologies'
update dbo.t11 set Sub_domain1='Water resource management'
update dbo.t11 set Sub_domain2='Supply-side technologies (water availability)'
update dbo.t11 set Sub_domain3='Water storage and distribution'

---------- 9.2.2.4 Water filtration; Water and wastewater treatment

drop table dbo.t12
select distinct CPC_class_symbol as 'Code'
into dbo.t12
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/152'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/20'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/204'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/208'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/211'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/212'

alter table dbo.t12 add 	Domain VARCHAR(300)
alter table dbo.t12 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t12 add 	Sub_domain3	VARCHAR(300)

update dbo.t12 set Domain='Climate change adaptation technologies'
update dbo.t12 set Sub_domain1='Water resource management'
update dbo.t12 set Sub_domain2='Supply-side technologies (water availability)'
update dbo.t12 set Sub_domain3='Water filtration; Water and wastewater treatment'

---------- 9.2.2.5 Protecting water resources

drop table dbo.t13
select distinct CPC_class_symbol as 'Code'
into dbo.t13
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/40'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/402'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/404'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/406'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/411'

alter table dbo.t13 add 	Domain VARCHAR(300)
alter table dbo.t13 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t13 add 	Sub_domain3	VARCHAR(300)

update dbo.t13 set Domain='Climate change adaptation technologies'
update dbo.t13 set Sub_domain1='Water resource management'
update dbo.t13 set Sub_domain2='Supply-side technologies (water availability)'
update dbo.t13 set Sub_domain3='Protecting water resources'


----------------------------------------------------------------------------------------------------------------------------
----- 9.3.  Adapting or protecting infrastructure or their operation
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t32
select distinct CPC_class_symbol as 'Code'
into dbo.t32
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30%'

alter table dbo.t32 add 	Domain VARCHAR(300)
alter table dbo.t32 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t32 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t32 add 	Sub_domain3	VARCHAR(300)

update dbo.t32 set Domain='Climate change adaptation technologies'
update dbo.t32 set Sub_domain1='Adapting or protecting infrastructure or their operation'
update dbo.t32 set Sub_domain2=NULL
update dbo.t32 set Sub_domain3=NULL

---------- 9.3.1. Extreme weather resilient electric power supply systems

drop table dbo.t14
select distinct CPC_class_symbol as 'Code'
into dbo.t14
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/14'

alter table dbo.t14 add 	Domain VARCHAR(300)
alter table dbo.t14 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t14 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t14 add 	Sub_domain3	VARCHAR(300)

update dbo.t14 set Domain='Climate change adaptation technologies'
update dbo.t14 set Sub_domain1='Adapting or protecting infrastructure or their operation'
update dbo.t14 set Sub_domain2='Extreme weather resilient electric power supply systems'
update dbo.t14 set Sub_domain3=NULL

---------- 9.3.2. Structural elements or technology for improving thermal insulation

drop table dbo.t15
select distinct CPC_class_symbol as 'Code'
into dbo.t15
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/24'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/242'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/244'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/249'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/254'

alter table dbo.t15 add 	Domain VARCHAR(300)
alter table dbo.t15 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t15 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t15 add 	Sub_domain3	VARCHAR(300)

update dbo.t15 set Domain='Climate change adaptation technologies'
update dbo.t15 set Sub_domain1='Adapting or protecting infrastructure or their operation'
update dbo.t15 set Sub_domain2='Structural elements or technology for improving thermal insulation'
update dbo.t15 set Sub_domain3=NULL

---------- 9.3.3. Relating to heating, ventilation or air conditioning [HVAC] technologies

drop table dbo.t16
select distinct CPC_class_symbol as 'Code'
into dbo.t16
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/27'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/272'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/274'

alter table dbo.t16 add 	Domain VARCHAR(300)
alter table dbo.t16 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t16 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t16 add 	Sub_domain3	VARCHAR(300)

update dbo.t16 set Domain='Climate change adaptation technologies'
update dbo.t16 set Sub_domain1='Adapting or protecting infrastructure or their operation'
update dbo.t16 set Sub_domain2='Relating to heating, ventilation or air conditioning [HVAC] technologies'
update dbo.t16 set Sub_domain3=NULL

---------- 9.3.4. In transportation

drop table dbo.t17
select distinct CPC_class_symbol as 'Code'
into dbo.t17
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/30'

alter table dbo.t17 add 	Domain VARCHAR(300)
alter table dbo.t17 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t17 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t17 add 	Sub_domain3	VARCHAR(300)

update dbo.t17 set Domain='Climate change adaptation technologies'
update dbo.t17 set Sub_domain1='Adapting or protecting infrastructure or their operation'
update dbo.t17 set Sub_domain2='In transportation'
update dbo.t17 set Sub_domain3=NULL

---------- 9.3.5. Planning or developing urban green infrastructure

drop table dbo.t18
select distinct CPC_class_symbol as 'Code'
into dbo.t18
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/60'

alter table dbo.t18 add 	Domain VARCHAR(300)
alter table dbo.t18 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t18 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t18 add 	Sub_domain3	VARCHAR(300)

update dbo.t18 set Domain='Climate change adaptation technologies'
update dbo.t18 set Sub_domain1='Adapting or protecting infrastructure or their operation'
update dbo.t18 set Sub_domain2='Planning or developing urban green infrastructure'
update dbo.t18 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 9.4.  Adaptation technologies in agriculture, forestry, livestock or agro-alimentary production
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t33
select distinct CPC_class_symbol as 'Code'
into dbo.t33
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40%'

alter table dbo.t33 add 	Domain VARCHAR(300)
alter table dbo.t33 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t33 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t33 add 	Sub_domain3	VARCHAR(300)

update dbo.t33 set Domain='Climate change adaptation technologies'
update dbo.t33 set Sub_domain1='Adaptation technologies in agriculture, forestry, livestock or agro-alimentary production'
update dbo.t33 set Sub_domain2=NULL
update dbo.t33 set Sub_domain3=NULL

---------- 9.4.1. In agriculture

drop table dbo.t19
select distinct CPC_class_symbol as 'Code'
into dbo.t19
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/10'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/13'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/132'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/135'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/138'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/146'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/20'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/22'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/25'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/28'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/51'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/58'

alter table dbo.t19 add 	Domain VARCHAR(300)
alter table dbo.t19 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t19 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t19 add 	Sub_domain3	VARCHAR(300)

update dbo.t19 set Domain='Climate change adaptation technologies'
update dbo.t19 set Sub_domain1='Adaptation technologies in agriculture, forestry, livestock or agro-alimentary production'
update dbo.t19 set Sub_domain2='In agriculture'
update dbo.t19 set Sub_domain3=NULL


---------- 9.4.2. Ecological corridors or buffer zones

drop table dbo.t20
select distinct CPC_class_symbol as 'Code'
into dbo.t20
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/60'

alter table dbo.t20 add 	Domain VARCHAR(300)
alter table dbo.t20 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t20 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t20 add 	Sub_domain3	VARCHAR(300)

update dbo.t20 set Domain='Climate change adaptation technologies'
update dbo.t20 set Sub_domain1='Adaptation technologies in agriculture, forestry, livestock or agro-alimentary production'
update dbo.t20 set Sub_domain2='Ecological corridors or buffer zones'
update dbo.t20 set Sub_domain3=NULL

---------- 9.4.3. In livestock or poultry

drop table dbo.t21
select distinct CPC_class_symbol as 'Code'
into dbo.t21
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/70'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/76'

alter table dbo.t21 add 	Domain VARCHAR(300)
alter table dbo.t21 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t21 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t21 add 	Sub_domain3	VARCHAR(300)

update dbo.t21 set Domain='Climate change adaptation technologies'
update dbo.t21 set Sub_domain1='Adaptation technologies in agriculture, forestry, livestock or agro-alimentary production'
update dbo.t21 set Sub_domain2='In livestock or poultry'
update dbo.t21 set Sub_domain3=NULL

---------- 9.4.4. In fisheries management

drop table dbo.t22
select distinct CPC_class_symbol as 'Code'
into dbo.t22
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/80'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/81'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/818'

alter table dbo.t22 add 	Domain VARCHAR(300)
alter table dbo.t22 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t22 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t22 add 	Sub_domain3	VARCHAR(300)

update dbo.t22 set Domain='Climate change adaptation technologies'
update dbo.t22 set Sub_domain1='Adaptation technologies in agriculture, forestry, livestock or agro-alimentary production'
update dbo.t22 set Sub_domain2='In fisheries management'
update dbo.t22 set Sub_domain3=NULL

---------- 9.4.5. In food processing or handling, e.g. food conservation

drop table dbo.t23
select distinct CPC_class_symbol as 'Code'
into dbo.t23
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/90'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/924'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/926'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/928'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/963'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/966'

alter table dbo.t23 add 	Domain VARCHAR(300)
alter table dbo.t23 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t23 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t23 add 	Sub_domain3	VARCHAR(300)

update dbo.t23 set Domain='Climate change adaptation technologies'
update dbo.t23 set Sub_domain1='Adaptation technologies in agriculture, forestry, livestock or agro-alimentary production'
update dbo.t23 set Sub_domain2='In food processing or handling, e.g. food conservation'
update dbo.t23 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 9.5.  Adaptation technologies in human health protection, e.g. against extreme weather
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t24
select distinct CPC_class_symbol as 'Code'
into dbo.t24
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  50/00'

alter table dbo.t24 add 	Domain VARCHAR(300)
alter table dbo.t24 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t24 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t24 add 	Sub_domain3	VARCHAR(300)

update dbo.t24 set Domain='Climate change adaptation technologies'
update dbo.t24 set Sub_domain1='Adaptation technologies in human health protection, e.g. against extreme weather'
update dbo.t24 set Sub_domain2=NULL
update dbo.t24 set Sub_domain3=NULL

---------- 9.5.1. Air quality improvement or preservation

drop table dbo.t25
select distinct CPC_class_symbol as 'Code'
into dbo.t25
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  50/20'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  50/2351'

alter table dbo.t25 add 	Domain VARCHAR(300)
alter table dbo.t25 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t25 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t25 add 	Sub_domain3	VARCHAR(300)

update dbo.t25 set Domain='Climate change adaptation technologies'
update dbo.t25 set Sub_domain1='Adaptation technologies in human health protection, e.g. against extreme weather'
update dbo.t25 set Sub_domain2='Air quality improvement or preservation'
update dbo.t25 set Sub_domain3=NULL

---------- 9.5.2. Against vector-borne diseases whose impact is exacerbated by climate change

drop table dbo.t26
select distinct CPC_class_symbol as 'Code'
into dbo.t26
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  50/30'

alter table dbo.t26 add 	Domain VARCHAR(300)
alter table dbo.t26 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t26 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t26 add 	Sub_domain3	VARCHAR(300)

update dbo.t26 set Domain='Climate change adaptation technologies'
update dbo.t26 set Sub_domain1='Adaptation technologies in human health protection, e.g. against extreme weather'
update dbo.t26 set Sub_domain2='Against vector-borne diseases whose impact is exacerbated by climate change'
update dbo.t26 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 9.6.  Technologies having an indirect contribution to adaptation to climate change
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t27
select distinct CPC_class_symbol as 'Code'
into dbo.t27
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  90/00'

alter table dbo.t27 add 	Domain VARCHAR(300)
alter table dbo.t27 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t27 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t27 add 	Sub_domain3	VARCHAR(300)

update dbo.t27 set Domain='Climate change adaptation technologies'
update dbo.t27 set Sub_domain1='Technologies having an indirect contribution to adaptation to climate change'
update dbo.t27 set Sub_domain2=NULL
update dbo.t27 set Sub_domain3=NULL

---------- 9.6.1. Information and communication technologies [ICT] supporting adaptation to climate change, e.g. for weather forecasting or climate simulation 

drop table dbo.t28
select distinct CPC_class_symbol as 'Code'
into dbo.t28
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  90/10'

alter table dbo.t28 add 	Domain VARCHAR(300)
alter table dbo.t28 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t28 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t28 add 	Sub_domain3	VARCHAR(300)

update dbo.t28 set Domain='Climate change adaptation technologies'
update dbo.t28 set Sub_domain1='Technologies having an indirect contribution to adaptation to climate change'
update dbo.t28 set Sub_domain2='Information and communication technologies [ICT] supporting adaptation to climate change, e.g. for weather forecasting or climate simulation'
update dbo.t28 set Sub_domain3=NULL

---------- 9.6.2.  Assessment of water resources

drop table dbo.t29
select distinct CPC_class_symbol as 'Code'
into dbo.t29
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  90/30'

alter table dbo.t29 add 	Domain VARCHAR(300)
alter table dbo.t29 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t29 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t29 add 	Sub_domain3	VARCHAR(300)

update dbo.t29 set Domain='Climate change adaptation technologies'
update dbo.t29 set Sub_domain1='Technologies having an indirect contribution to adaptation to climate change'
update dbo.t29 set Sub_domain2='Assessment of water resources'
update dbo.t29 set Sub_domain3=NULL

---------- 9.6.3.  Monitoring or fighting invasive species

drop table dbo.t30
select distinct CPC_class_symbol as 'Code'
into dbo.t30
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  90/40'

alter table dbo.t30 add 	Domain VARCHAR(300)
alter table dbo.t30 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t30 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t30 add 	Sub_domain3	VARCHAR(300)

update dbo.t30 set Domain='Climate change adaptation technologies'
update dbo.t30 set Sub_domain1='Technologies having an indirect contribution to adaptation to climate change'
update dbo.t30 set Sub_domain2='Monitoring or fighting invasive species'
update dbo.t30 set Sub_domain3=NULL


drop table dbo.adaptation
select * 
into dbo.adaptation
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=33)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.adaptation'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

----------------------------------------- 10. SUSTAINABLE OCEAN ECONOMY --------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------
----- 10.1.  Ocean renewable energy generation
----------------------------------------------------------------------------------------------------------------------------

---------- 10.1.1 Offshore wind energy 

drop table dbo.t1
select distinct CPC_class_symbol as 'Code'
into dbo.t1
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/727'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F03D  13/25'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F05B2240/95'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B2035/446'

alter table dbo.t1 add 	Domain VARCHAR(300)
alter table dbo.t1 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t1 add 	Sub_domain3	VARCHAR(300)

update dbo.t1 set Domain='Sustainable ocean economy'
update dbo.t1 set Sub_domain1='Ocean renewable energy generation'
update dbo.t1 set Sub_domain2='Offshore wind energy'
update dbo.t1 set Sub_domain3=NULL

---------- 10.1.2 Offshore solar energy 

drop table dbo.t2
select distinct CPC_class_symbol as 'Code'
into dbo.t2
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B2035/4453'

alter table dbo.t2 add 	Domain VARCHAR(300)
alter table dbo.t2 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t2 add 	Sub_domain3	VARCHAR(300)

update dbo.t2 set Domain='Sustainable ocean economy'
update dbo.t2 set Sub_domain1='Ocean renewable energy generation'
update dbo.t2 set Sub_domain2='Offshore solar energy'
update dbo.t2 set Sub_domain3=NULL


---------- 10.1.3 Tide, wave, current and other marine energy 

drop table dbo.t3
select distinct CPC_class_symbol as 'Code'
into dbo.t3
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B   9/08'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F03B  13/12'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F03B  13/14%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F03B  13/16'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F03B  13/18%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F03B  13/2%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B2035/4466'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02E  10/30'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'F03G   7/05'

alter table dbo.t3 add 	Domain VARCHAR(300)
alter table dbo.t3 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t3 add 	Sub_domain3	VARCHAR(300)

update dbo.t3 set Domain='Sustainable ocean economy'
update dbo.t3 set Sub_domain1='Ocean renewable energy generation'
update dbo.t3 set Sub_domain2='Tide, wave, current and other marine energy'
update dbo.t3 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 10.2.  Ocean pollution abatement
----------------------------------------------------------------------------------------------------------------------------

---------- 10.2.1 Ballast water treatment

drop table dbo.t4
select distinct CPC_class_symbol as 'Code'
into dbo.t4
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C02F2103/008'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63J   4/002'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63J   4/004'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63J   4/006'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B  13%'


alter table dbo.t4 add 	Domain VARCHAR(300)
alter table dbo.t4 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t4 add 	Sub_domain3	VARCHAR(300)

update dbo.t4 set Domain='Sustainable ocean economy'
update dbo.t4 set Sub_domain1='Ocean pollution abatement'
update dbo.t4 set Sub_domain2='Ballast water treatment'
update dbo.t4 set Sub_domain3=NULL


---------- 10.2.2 Oil spill (and other floating debris) prevention and cleanup

drop table dbo.t5
select distinct CPC_class_symbol as 'Code'
into dbo.t5
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B  25/082'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B  17/0036'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B  27/34'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'B63B  35/32'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C09K   3/32'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E21B  43/0122'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B  15/04%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B  15/06'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B  15/08%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'E02B  15/1%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/204'

alter table dbo.t5 add 	Domain VARCHAR(300)
alter table dbo.t5 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t5 add 	Sub_domain3	VARCHAR(300)

update dbo.t5 set Domain='Sustainable ocean economy'
update dbo.t5 set Sub_domain1='Ocean pollution abatement'
update dbo.t5 set Sub_domain2='Oil spill (and other floating debris) prevention and cleanup'
update dbo.t5 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 10.3.  Climate change mitigation in maritime transport
----------------------------------------------------------------------------------------------------------------------------

---------- 10.3.1 Improved vessel design

drop table dbo.t6
select distinct CPC_class_symbol as 'Code'
into dbo.t6
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  70/10'

alter table dbo.t6 add 	Domain VARCHAR(300)
alter table dbo.t6 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t6 add 	Sub_domain3	VARCHAR(300)

update dbo.t6 set Domain='Sustainable ocean economy'
update dbo.t6 set Sub_domain1='Climate change mitigation in maritime transport'
update dbo.t6 set Sub_domain2='Improved vessel design'
update dbo.t6 set Sub_domain3=NULL

---------- 10.3.2 Fuel-efficient propulsion or fuel substitution

drop table dbo.t7
select distinct CPC_class_symbol as 'Code'
into dbo.t7
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  70/50'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  70/5218'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02T  70/5236'

alter table dbo.t7 add 	Domain VARCHAR(300)
alter table dbo.t7 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t7 add 	Sub_domain3	VARCHAR(300)

update dbo.t7 set Domain='Sustainable ocean economy'
update dbo.t7 set Sub_domain1='Climate change mitigation in maritime transport'
update dbo.t7 set Sub_domain2='Fuel-efficient propulsion or fuel substitution'
update dbo.t7 set Sub_domain3=NULL


----------------------------------------------------------------------------------------------------------------------------
----- 10.4.  Climate change mitigation and adaptation in fishing, aquaculture and aquafarming
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t8
select distinct CPC_class_symbol as 'Code'
into dbo.t8
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02P  60/60'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/80'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/81'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  40/818'

alter table dbo.t8 add 	Domain VARCHAR(300)
alter table dbo.t8 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t8 add 	Sub_domain3	VARCHAR(300)

update dbo.t8 set Domain='Sustainable ocean economy'
update dbo.t8 set Sub_domain1='Climate change mitigation and adaptation in fishing, aquaculture and aquafarming'
update dbo.t8 set Sub_domain2=NULL
update dbo.t8 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 10.5.  Desalination of sea water
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t9
select distinct CPC_class_symbol as 'Code'
into dbo.t9
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/124'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/131'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/138'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/141'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/142'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/144'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'C02F   1/265'

alter table dbo.t9 add 	Domain VARCHAR(300)
alter table dbo.t9 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t9 add 	Sub_domain3	VARCHAR(300)

update dbo.t9 set Domain='Sustainable ocean economy'
update dbo.t9 set Sub_domain1='Desalination of sea water'
update dbo.t9 set Sub_domain2=NULL
update dbo.t9 set Sub_domain3=NULL

----------------------------------------------------------------------------------------------------------------------------
----- 10.6.  Climate change adaptation in coastal zones
----------------------------------------------------------------------------------------------------------------------------

drop table dbo.t10
select distinct CPC_class_symbol as 'Code'
into dbo.t10
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  10%'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  20/404'
UNION
select distinct CPC_class_symbol as 'Code'
from [PATSTAT].[dbo].[AUTUMN21_APPLN_CPC]
where CPC_class_symbol like 'Y02A  30/14'

alter table dbo.t10 add 	Domain VARCHAR(300)
alter table dbo.t10 add 	Sub_domain1	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain2	VARCHAR(300)
alter table dbo.t10 add 	Sub_domain3	VARCHAR(300)

update dbo.t10 set Domain='Sustainable ocean economy'
update dbo.t10 set Sub_domain1='Climate change adaptation in coastal zones'
update dbo.t10 set Sub_domain2=NULL
update dbo.t10 set Sub_domain3=NULL

drop table dbo.ocean
select * 
into dbo.ocean
from dbo.t1
drop table dbo.t1

declare @i INT
declare @j varchar(2)
declare @t varchar(5)
declare @table varchar(50)
declare @drop varchar(50)
set @i = 2
set @j = '2'
set @t='dbo.t'
WHILE (@i <=10)
BEGIN 
	set @j=@i
	set @table=('insert' + ' '+'into'+ ' ' +'dbo.ocean'+ ' ' +'select'+' '+'*'+' '+'from'+ ' '+@t+@j)
	execute(@table)
    set @drop=('drop'+' '+'table'+' '+@t+@j)
    execute(@drop)
    SET @i = @i + 1
END
GO


----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
-------------------------------- MERGE ALL TABLES AND CREATE THE FINAL ONE (CPC codes) -------------------------------------
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

use EPI
drop table dbo.ENV_tech_CPC 
select *
into dbo.ENV_tech_CPC
from dbo.management
UNION
select * from dbo.energy
UNION
select * from dbo.ghg
UNION
select * from dbo.transport
UNION
select * from dbo.buildings
UNION
select * from dbo.waterwaste
UNION
select * from dbo.goods
UNION
select * from dbo.ict
UNION
select * from dbo.adaptation
UNION
select * from dbo.ocean

drop table dbo.management
drop table dbo.energy
drop table dbo.ghg
drop table dbo.transport
drop table dbo.buildings
drop table dbo.waterwaste
drop table dbo.goods
drop table dbo.ict
drop table dbo.adaptation
drop table dbo.ocean