Order by with Union

Question

I have the following tables:

Pessoa1
Id  |    Nome      |  Cidade
1   |   José       |São Paulo
2   |   Maria      |Rio de Janeiro

Pessoa2
Id  |    Nome      |  Cidade
1   |   Pedro      |Belo Horizonte
2   |   Ana        |Vitória

I have the following select:

SELECT * FROM (

    SELECT
          p1.Nome
          ISNULL(CAST(p1.Nome AS VARCHAR),'') + ';' + 
          ISNULL(CAST(p1.Cidade AS VARCHAR),'') AS Resultado
    FROM Pessoa1 p1

    UNION

    SELECT
          p2.Nome
          ISNULL(CAST(p2.Nome AS VARCHAR),'') + ';' + 
          ISNULL(CAST(p2.Cidade AS VARCHAR),'') AS Resultado
    FROM Pessoa1 p2

) AS Resultado
ORDER BY Resultado.Nome

The goal is to bring the column Resultado with values separated by semicolon, respecting the alphabetical order through the column Nome.

The point is that I would like the column Nome did not appear when executing the select, because it is already included in the column Resultado. Is there any way?

Answer 1

You are using the dial *, which means all fields. Change your SQL from:

SELECT * FROM (

for

SELECT Resultado FROM (

So only this field will be returned.