Using Pivot to remove duplicated results adding new columns

Question

I have the following table

[_teste_calendario_Table]

and when "Monstrengo" has the same value, I'd like to make a pivot, adding new columns "De1", "Ate1", "Vencimento1", "De2", "Ate2", "Vencimento2" with the values from "De" "Ate" "Vencimento" of the duplicated rows (when I say duplicated rows, I mean rows with the same "Monstrengo" values). The expected result would be something like:

[expected_output]

. I have tried to achieve this result in two different (but similar) ways. Firstly, I tried selecting [BBLA] when [BBLA] <> 1 and creating the new columns with the values of the duplicate ones. The code went something like this:

select *
,MAX(CASE WHEN BBLA = 2 THEN de END) AS De1
,MAX(CASE WHEN BBLA= 2 THEN Ate END) AS Ate1
,MAX(CASE WHEN BBLA= 2 THEN Vencimento END) AS Vencimento1
,MAX(CASE WHEN BBLA= 3 THEN de END) AS De2
,MAX(CASE WHEN BBLA= 3 THEN Ate END) AS Ate2
,MAX(CASE WHEN BBLA= 3 THEN Vencimento END) AS Vencimento2
from _TESTE_calendario_cliente_habilitado
GROUP BY pessoaid,BBLA,monstrengo,BBLA,DiasParaCliente,DiaInicioSemana
,De,Ate,Vencimento

I expected that with this the values of the duplicate rows would be translated to the new columns, and the old rows would be removed; the thing is that they weren't. I managed to add the values to the new columns, but the duplicated weren't removed and the values were added to them, not the original one. The result when like this:

[first_Test]

I thought about using pivot, but for what I have seen, I would need a single value that to pivot (only [De], for example); since I want to do it with De, Ate, Vencimento, I don't know how to use apply pivot. Since it didn't work, I created a second table, very similar to the first, but with a second BBLA variable (BBLA_Verificador) that takes the LEAD value of BBLA (the BBLA value in the row below of the current one), so that I would be able to know if the next row was a duplicate or not. I then used the same function them before to try and get the values on the correct row. Obviously it didn't work. I managed to put the values in the first row (not the duplicates) but the values were the same as the first rows, and not the values of the duplicated ones. I also didn't menage to remove the old ones. the result was this: [_teste_calendario_Table_2]

How am I supposed to do it?

Edit: To be clearer, this is an example of what my query is returning: 3 lines for the same item in Monstrengo, each line there is a separate block for De,Ate,Vencimento;De1,ate1,Vencimento1 and De2,Ate2,Vencimento2. What I have What I wish is: a single line for each Monstrengo, and if there is a second or a third set of values for De,Ate,Vencimento, that they would enter in the single Monstrengo line. What I wish

Answer 1

I managed to find a solution; I had already tried something like it, but I problaby made a mistake with the syntax. I changed the code

select *
,MAX(CASE WHEN BBLA = 2 THEN de END) AS De1
,MAX(CASE WHEN BBLA= 2 THEN Ate END) AS Ate1
,MAX(CASE WHEN BBLA= 2 THEN Vencimento END) AS Vencimento1
,MAX(CASE WHEN BBLA= 3 THEN de END) AS De2
,MAX(CASE WHEN BBLA= 3 THEN Ate END) AS Ate2
,MAX(CASE WHEN BBLA= 3 THEN Vencimento END) AS Vencimento2
from _TESTE_calendario_cliente_habilitado
GROUP BY pessoaid,BBLA,monstrengo,BBLA,DiasParaCliente,DiaInicioSemana
,De,Ate,Vencimento

to

SELECT *
,CASE WHEN LEAD(BBLA,1) OVER (PARTITION BY MONSTRENGO ORDER BY BBLA) = 2 THEN LEAD(de,1) OVER
(PARTITION BY MONSTRENGO ORDER BY BBLA) END AS De1
,CASE WHEN LEAD(BBLA,1) OVER (PARTITION BY MONSTRENGO ORDER BY BBLA)= 2 THEN LEAD(Ate,1) OVER
(PARTITION BY MONSTRENGO ORDER BY BBLA) END AS Ate1
,CASE WHEN LEAD(BBLA,1) OVER (PARTITION BY MONSTRENGO ORDER BY BBLA)= 2 THEN LEAD(Vencimento,1) OVER
(PARTITION BY MONSTRENGO ORDER BY BBLA) END AS Vencimento1
--
,CASE WHEN LEAD(BBLA,2) OVER (PARTITION BY MONSTRENGO ORDER BY BBLA)= 3 THEN LEAD(de,2) OVER
(PARTITION BY MONSTRENGO ORDER BY BBLA) END AS De2
,CASE WHEN LEAD(BBLA,2) OVER (PARTITION BY MONSTRENGO ORDER BY BBLA)= 3 THEN LEAD(Ate,2) OVER
(PARTITION BY MONSTRENGO ORDER BY BBLA) END AS Ate2
,CASE WHEN LEAD(BBLA,2) OVER (PARTITION BY MONSTRENGO ORDER BY BBLA)= 3 THEN LEAD(Vencimento,2) OVER
(PARTITION BY MONSTRENGO ORDER BY BBLA) END AS Vencimento2
FROM _TESTE_calendario_cliente_habilitado
GROUP BY _TESTE_calendario_cliente_habilitado.pessoaid, _TESTE_calendario_cliente_habilitado.Monstrengo
,_TESTE_calendario_cliente_habilitado.BBLA,_TESTE_calendario_cliente_habilitado.DiasParaCliente,
_TESTE_calendario_cliente_habilitado.DiaInicioSemana,_TESTE_calendario_cliente_habilitado.De
,_TESTE_calendario_cliente_habilitado.Ate,_TESTE_calendario_cliente_habilitado.Vencimento

This way I managed to get everything in the same line; I created a table with this information, and then created a view, filtering by BBLA = 1, giving me all non-duplicate rows.