SQL - Select with possible concatenation

Question

I created a query that concatenates my results. It is a table with names and to which range (square) they are classified. The table is called consulta_readiness and the fields are name and squared:

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]

Since I only have the option of 9 possible squares, the query comes like this, for example:

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 it a little differently: that there were nine result columns called Square 1, Square 2, Square 3, ... , Square 9. The SELECT query would only come with a row like this, for example:

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 any idea how I can do it? It’s also kind of a concatenation, I think. But I haven’t figured out the best way yet.

Answer 1

I think you want to turn rows into columns. There are already several topics that address the same subject. See one of them here.

I made an example using oracle, which you can use as a basis for your database:

create table consulta_prontidao(nome VARCHAR(30), quadrado VARCHAR(30));


insert into consulta_prontidao values ('1', NULL);

insert into consulta_prontidao values ('2','Maria');
insert into consulta_prontidao values ('2','Tamiris');
insert into consulta_prontidao values ('3','Kellen');
insert into consulta_prontidao values ('4','Elis');
insert into consulta_prontidao values ('5','Paulo');
insert into consulta_prontidao values ('5','Alex');
insert into consulta_prontidao values ('6','Mauro');
insert into consulta_prontidao values ('6','Rodrigo');
insert into consulta_prontidao values ('6','Elaine');
insert into consulta_prontidao values ('6','Gabriela');
insert into consulta_prontidao values ('7', NULL);
insert into consulta_prontidao values ('8', 'Teixeira');
insert into consulta_prontidao values ('8', 'Luis');
insert into consulta_prontidao values ('9', NULL);

After the mounted table, just do the string concatenation (in this case, the LISTAGG) along with the pivot

select * from consulta_prontidao
pivot (
       LISTAGG(quadrado, ',') WITHIN GROUP (ORDER BY quadrado)
       for nome in ('1'  as QUADRADO_1,
                    '2'  as QUADRADO_2,
                    '3'  as QUADRADO_3,
                    '4'  as QUADRADO_4,
                    '5'  as QUADRADO_5,
                    '6'  as QUADRADO_6,
                    '7'  as QUADRADO_7,
                    '8'  as QUADRADO_8,
                    '9'  as QUADRADO_9
      ));

Upshot

EDIT:

Apparently you work with MySql, then I suggest using the GROUP_CONCAT()

Thus remaining:

select * from consulta_prontidao
pivot (
       group_concat(quadrado ',')
       for nome in ('1'  as QUADRADO_1,
                    '2'  as QUADRADO_2,
                    '3'  as QUADRADO_3,
                    '4'  as QUADRADO_4,
                    '5'  as QUADRADO_5,
                    '6'  as QUADRADO_6,
                    '7'  as QUADRADO_7,
                    '8'  as QUADRADO_8,
                    '9'  as QUADRADO_9
      ));

I have no way to test the implementation in Mysql. Test and let me know if you have a problem that I can fix it.

In SqlServer 2016 use STRING_AGG:

select * from consulta_prontidao
pivot (
       STRING_AGG (quadrado, ',')
       for nome in ('1'  as QUADRADO_1,
                    '2'  as QUADRADO_2,
                    '3'  as QUADRADO_3,
                    '4'  as QUADRADO_4,
                    '5'  as QUADRADO_5,
                    '6'  as QUADRADO_6,
                    '7'  as QUADRADO_7,
                    '8'  as QUADRADO_8,
                    '9'  as QUADRADO_9
      ));