--- 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