How can I merge this query so that it returns only one column?

I have the following table:

Player Name         Nationality
--------------------------------
Romelu Lukaku       Belgian
Christian Pulisic   American
Kai Havertz         German
Masoun Mount        British
Reece James         British
Edouard Mendy       French
N'Golo Kante        French
Hakim Ziyech        Morrocan
Timo Werner         German
Thiago Silva        Brazilian
Ross Barkley        British

​x
 
Player Name         Nationality--------------------------------Romelu Lukaku       BelgianChristian Pulisic   AmericanKai Havertz         GermanMasoun Mount        BritishReece James         BritishEdouard Mendy       FrenchN'Golo Kante        FrenchHakim Ziyech        MorrocanTimo Werner         GermanThiago Silva        BrazilianRoss Barkley        British​

I tried the following query:

SELECT 
    REPLACE([Player Name], 'Reece', 'John'),
    REPLACE([Player Name], 'Christian', 'Chris') 
FROM 
    Players

 
SELECT     REPLACE([Player Name], 'Reece', 'John'),    REPLACE([Player Name], 'Christian', 'Chris') FROM     Players​

This query returns two columns rather than just one. How can I make the query one so that only one column gets returned?

Answer

You can use UNION tojoin rows vertically

SELECT REPLACE([Player Name], 'Reece', 'John') as name FROM Players
UNION
SELECT REPLACE([Player Name], 'Christian', 'Chris') FROM Players

 
SELECT REPLACE([Player Name], 'Reece', 'John') as name FROM PlayersUNIONSELECT REPLACE([Player Name], 'Christian', 'Chris') FROM Players​

Advertisement

Answer