title: Using Pivot to remove duplicated results adding new columns
body: <p>I have the following table</p>
  <p>[_teste_calendario_Table]</p>
  <p><img src="https://i.stack.imgur.com/vzhya.png" alt="1" /></p>
  <p>and when &quot;Monstrengo&quot; has the same value, I'd like to make a pivot, adding new columns &quot;De1&quot;, &quot;Ate1&quot;, &quot;Vencimento1&quot;,
  &quot;De2&quot;, &quot;Ate2&quot;, &quot;Vencimento2&quot; with the values from &quot;De&quot; &quot;Ate&quot; &quot;Vencimento&quot; of the duplicated rows (when I say duplicated rows, I mean rows with the same &quot;Monstrengo&quot; values). The expected result would be something like:</p>
  <p>[expected_output]</p>
  <p><img src="https://i.stack.imgur.com/yyBUt.png" alt="2" /></p>
  <p>. I have tried to achieve this result in two different (but similar) ways. Firstly, I tried selecting [BBLA] when [BBLA] &lt;&gt; 1 and creating the new columns with the values of the duplicate ones. The code went something like this:</p>
  <pre><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
  </code></pre>
  <p>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:</p>
  <p>[first_Test]
  <img src="https://i.stack.imgur.com/2Nrx6.png" alt="3" /></p>
  <p>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]</p>
  <p><img src="https://i.stack.imgur.com/bo1Vf.png" alt="4" /></p>
  <p>How am I supposed to do it?</p>
  <p>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.
  <a href="https://i.stack.imgur.com/o6LiB.png" rel="nofollow noreferrer">What I have</a>
  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.
  <a href="https://i.stack.imgur.com/0uLw3.png" rel="nofollow noreferrer">What I wish</a></p>