pivot & unpivot SQL

Question

I've been stuck on this for sometime now. I have a Healthcare_WorldBank.dbo.wb_new table with country_name (varchar), country_code (varchar), series_code (varchar), yr_2015 (float), yr_2016 (float), yr_2017 (float). I have been trying to unpivot the table to create a table with country_name, country_code, year (will be 2015, 2016, 2017), the series_code spread out as extra columns with the corresponding values of yr_2015, yr_2016, yr_2017

This is the table I have

country_name	country_code	series_code	yr_2015	yr_2016	yr_2017
Algeria	DZA	CHE	6.98	6.61	6.28
Algeria	DZA	CAPHE	2.61	2.71	2.79
Algeria	DZA	CHE_CAP	2.91	2.74	2.62
Algeria	DZA	CHE_CAP_PPP	5.73	5.56	6.13

This is the table I am trying to recreate

country_name	country_code	year	CHE	CAPHE	CHE_CAP	CHE_CAP_PPP
Algeria	DZA	2015	6.98	2.61	2.91	5.73
Algeria	DZA	2016	6.61	2.71	2.74	5.56
Algeria	DZA	2017	6.28	2.79	2.62	6.13

I have tried union all but the values don't match up.

Answer 1

Unpivot years, then pivot series codes:

dbfiddle demo

select country_name, country_code, substring(yr, 4, 4) as yr, 
       [CHE], [CAPHE], [CHE_CAP], [CHE_CAP_PPP]  
from (
  select country_name, country_code, series_code, val, yr from wb_new
  unpivot (val for yr in (yr_2015, yr_2016, yr_2017)) as unpvt ) u
pivot ( max(val) 
  for series_code in ([CHE], [CAPHE], [CHE_CAP], [CHE_CAP_PPP])) pvt;