unpivot-postgres.sql

gistfile1.sql

SELECT
        'CyberSecurityIndex' AS "index",
	"CyberSecurityIndex"."Country" AS "Country",
	"CyberSecurityIndex"."iso3" AS "iso3",
	CAST("CyberSecurityIndex"."Year" AS TEXT) AS "Year",
	t.subindex AS subindex,
	CAST(t.value AS float) AS value
FROM "indices"."009_cybersecurityindex" "CyberSecurityIndex"
cross join lateral (
	values
		("CyberSecurityIndex"."National Cyber Security Index", 'National Cyber Security Index'),
	     ("CyberSecurityIndex"."Digital Development Level", 'Digital Development Level'),
    	 ("CyberSecurityIndex"."Difference", 'Difference NCSI-DPL')
) as t(value, subindex)

UNION

SELECT 
	'PressfreeomdIndex' AS "index",
	"pressfreedomindex"."countryname" as "Country",
	"pressfreedomindex"."iso3" as "iso3",
    CAST("pressfreedomindex"."year" AS TEXT) AS "Year",
	NULL AS "subindex",
	CAST(indexvalue AS float) AS value
FROM "indices"."001_pressfreedomindex" "pressfreedomindex"