GV9

SELECT '1' as "Order", IIF(GV9_1='0', null, GV9_1) as "UWW", IIF(GV9_2='0', null, GV9_2) as "UD", IIF(GV9_3='0', null, GV9_3) as "SWO", 'Rivers' as "Name", countryCode as "country_code"
FROM [UWWTD].[v1r2].[CP_source_WFD2016]
UNION
SELECT '2' as "Order", IIF(GV9_4='0', null, GV9_4) as "UWW", IIF(GV9_5='0', null, GV9_5) as "UD", IIF(GV9_6='0', null, GV9_6) as "SWO", 'Lakes' as "Name", countryCode as "country_code"
FROM [UWWTD].[v1r2].[CP_source_WFD2016]
UNION
SELECT '3' as "Order", IIF(GV9_7='NULL', null, GV9_7) as "UWW", IIF(GV9_8='NULL', null, GV9_8) as "UD", IIF(GV9_9='NULL', null, GV9_9) as "SWO", 'Transitional waters' as "Name", countryCode as "country_code"
FROM [UWWTD].[v1r2].[CP_source_WFD2016]
UNION
SELECT '4' as "Order", IIF(GV9_10='NULL', null, GV9_10) as "UWW", IIF(GV9_11='NULL', null, GV9_11) as "UD", IIF(GV9_12='NULL', null, GV9_12) as "SWO", 'Coastal waters' as "Name", countryCode as "country_code"
FROM [UWWTD].[v1r2].[CP_source_WFD2016]
UNION
SELECT '5' as "Order", IIF(GV9_13='0', null, GV9_13) as "UWW", IIF(GV9_14='0', null, GV9_14) as "UD", IIF(GV9_15='0', null, GV9_15) as "SWO", 'Groundwaters' as "Name", countryCode as "country_code"
FROM [UWWTD].[v1r2].[CP_source_WFD2016]
order by country_code