SQL Server: Colocar Resultados na mesma linha (Funções FOR XML PATH e STUFF)

por

Por vezes, existe necessidade de colocarmos os resultados de um query todos na mesma linha, como ocorre quando elaboramos um query para, por exemplo, obter todos os intervenientes de um contrato.

Consideremos então o seguinte query na base de dados AdventureWorks2019:

select FirstName, MiddleName, LastName
FROM AdventureWorks2019.Person.Person
WHERE BusinessEntityID < = 3

O resultado será o seguinte:

Uma forma relativamente simples de fazermos esta transformação, é recorrendo às funções STUFF e FOR XML PATH.

A função FOR XML PATH

A função FOR XML PATH, é bastante útil e permite transformar o resultado do nosso query no formato XML, sendo possível passar qual a tag que será usada para identificar cada registo.

Assim, caso queiramos ter cada registo separado por uma Tag “Cliente”, podemos fazer assim:

select FirstName, MiddleName, LastName
FROM AdventureWorks2019.Person.Person
WHERE BusinessEntityID < = 3
FOR XML PATH('Cliente')

O resultado é o seguinte:

No entanto sendo o nosso objetivo apenas separar os nomes por uma vírgula, devemos então passar este argumento com ”, por forma a que não colocada qualquer tag entre os nossos resultados e concatenar uma virgula para separar os diversos nomes.

Assim, criando algo deste género:

select ', ' + ISNULL(FirstName, '') + ISNULL(MiddleName, '') + ISNULL(LastName, '')
FROM AdventureWorks2019.Person.Person
WHERE BusinessEntityID < = 3
FOR XML PATH('')

Temos quase o resultado pretendido:

, Ken J Sánchez, Terri Lee Duffy, Roberto Tamburello

Como concatenámos um elemento e não especificámos nenhum nome para a coluna, repare-se que as tags que indicavam os nomes foram também removidas, o que permitiu aproximarmo-nos do nosso objetivo, agora falta retirar a virgula inicial.

A Função STUFF

De uma forma literal, esta função permite inserir uma string dentro de outra, a sua sintaxe é a seguinte:

STUFF(string inicial, posicaoinicial, numero caracteres a eliminar, expressao a inserir)

Para melhor entender, imagine que tendo a seguinte expressão:

FabioDomingos.com

Pretende inserir Marques entre o Fabio e o Domingos, temos então:

string inicial: FabioDomingos.com

posicaoinicial: 6

Caracteres a eliminar: 0

Expressão a Inserir: Marques

Assim o query deverá ser construído da seguinte forma:

SELECT STUFF('FabioDomingos.com',6,0,'Marques')

O resultado será então o pretendido:

FabioMarquesDomingos.com

Combinando tudo

O nosso query final, será algo do seguinte género:

SELECT STUFF((SELECT ', ' + REPLACE(ISNULL(FirstName, '') + ' ' + ISNULL(MiddleName, '') + ' ' + ISNULL(LastName, ''), ' ', '')
FROM AdventureWorks2019.Person.Person
WHERE BusinessEntityID < = 3
FOR XML PATH('')), 1, 2, '')

Basicamente, indicando 2 no número de caracteres a eliminar eliminamos a virgula e o primeiro espaço a seguir a esta.

O resultado então será o que pretendemos:

Ken J Sánchez, Terri Lee Duffy, RobertoTamburello

Deixe um comentário