I created a query that concatenates my results. It is a table with names and which range (square) they are sorted. The table is named prompt_name and the fields are name and square :
Select Main.Quadrado,
Left(Main.Nome,Len(Main.Nome)-1) As "Nome"
From
(
Select distinct ST2.Quadrado,
(
Select ST1.Nome + ','
From dbo.Consulta_Prontidao ST1
Where ST1.Quadrado = ST2.Quadrado
AND ST1.codigoUnidade = 45
AND ST1.codigoPeriodo = 5
ORDER BY ST1.Quadrado
For XML PATH ('')
) [Nome]
From dbo.Consulta_Prontidao ST2
) [Main]
Given that I have only the option of 9 possible squares, the query comes like this:
Quadrado | Nomes -------- | ----- 1 | NULL 2 | Maria,Tamiris 3 | Kellen 4 | Elis 5 | Paulo,Alex 6 | Mauro,Rodrigo,Elaine,Gabriela 7 | NULL 8 | Teixeira,Luis 9 | NULL
But I would like to do a bit differently: that there were nine result columns called Square 1, Square 2, Square 3, ..., Square 9. The SELECT query would only come with a line like this:
Quadrado 1 | Quadrado 2 | Quadrado 3 | Quadrado 4 | Quadrado 5 | Quadrado 6 | Quadrado 7 | Quadrado 8 | Quadrado 9 ---------- | ------------- | ---------- | ---------- | ---------- | ----------------------------- | ---------- | -------------- | ---------- NULL | Maria,Tamiris | Kellen | Elis | Paulo,Alex | Mauro,Rodrigo,Elaine,Gabriela | NULL | Teixeira, Luis | NULL
Does anyone have an idea how I can do it? It's also sort of a concatenation, I think. But I have not figured out the best way yet.
