Need to add new column in SQL View and get the value from another row based on condition-CodePudding

There is a table where one of the column stores their local language values and I would like to add another column in SQL view which will have values in English only which we can get it from other country.

here's the sample data

id	country	name
1	Belgium	Product
1	Italy	Prodotto
1	Spain	Product
2	Belgium	Used Car
2	Italy	Usato
2	Spain	Used Car

Expected Results :

id	country	name	English Name
1	Belgium	Product	Product
1	Italy	Prodotto	Product
1	Spain	Product	Product
2	Belgium	Used Car	Used Car
2	Italy	Usato	Used Car
2	Spain	Used Car	Used Car

Please help me with adding new column "English Name" in SQL View which can be derived from other countries. So here, Only Italy will have value other than English so in case Country Italy , I need to get the value from either Beligum/Spain.

Thanks in advance!

CodePudding user response：

You can use the FIRST_VALUE window function to select the name by ordering on when your country is Italy (NULL values are ordered first).

SELECT *, FIRST_VALUE(name) OVER(
              PARTITION BY id 
              ORDER     BY CASE WHEN country = 'Italy' THEN 0 END
          ) AS EnglishName 
FROM tab

Check the demo here.