Help with a PIVOT ( Sql Server )

Question

Guys I looked for many examples of Pivot but I could not understand... and I’m just not able to use in my select... what happens, I’m having this result :

+---------------------------------------------------------------------------------------------------+-------------------------------------------+
|  Controle     | Colonia     |    Nome    |  Matricula| QtaPessoa | Tipo Hospedagem                | NumeroVoucher | valorvoucher| Tipovoucher |
+---------------|-------------|------------|-----------|-----------|--------------------------------+---------------|-------------|-------------|
|  114539       | Campos      |   Cassio   |  1234567  | 2         | Associado a partir 12 anos     | 987654321     | 158,90      | Evento      |
|  114539       | Campos      |   Cassio   |  1234567  | 2         | Associado de 12 anos           | 123456789     | 100,00      | Promoçao    |
|  114539       | Campos      |   Cassio   |  1234567  | 3         | Associado de 7 a 11 anos       | 123456789     | 100,00      | Promoçao    |
+---------------------------------------------------------------------------------------------------+-------------------------------------------+

And I wish I had that result

+--------------------------------------------------------------------------------------------------------------------++-------------------------------------------+
|  Controle| Colonia| Nome |  Matricula|| Associado a partir 12 anos| Associado de 12 anos| Associado de 7 a 11 anos || NumeroVoucher | valorvoucher| Tipovoucher |
+----------|--------|------|-----------||----------------------------------------------------------------------------++---------------|-------------|-------------|
|  114539  | Campos |Cassio|  1234567  ||             2             |       NULL          |         NULL           | 987654321      | 158,90      | Evento      |
|  114539  | Campos |Cassio|  1234567  ||             NULL          |       2             |         3                  | 123456789      | 100,00      | Promoçao    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 

My select would be

SELECT distinct
    a.idEmpregadoReserva as Controle
    ,(select EmpresaAplicativo from tbEmpresaAplicativo where tbEmpresaAplicativo.idEmpresaAplicativo = c.idEmpresaAplicativo) Colonia
    ,d.Nome
    ,d.Matricula
    ,a.NumeroDocumento as [Numero Voucher]
    ,e.ValorVoucher
    ,f.TipoVoucher 
    ,(select tipoformapagto from tbHPG_TipoFormaPagto where tbHPG_TipoFormaPagto.idTipoFormaPagto = a.idTipoFormaPagto) [Forma Pagamento]
  FROM [APCEF].[dbo].[tbHPG_EmpregadoReserva_Financeiro] a join tbHPG_EmpregadoReserva_Reserva b on a.idEmpregadoReserva = b.idEmpregadoReserva
   join tbHPG_Reserva c on b.idReserva = c.idReserva join tbEmpregado d on c.idEmpregado = d.idEmpregado join tbEmpregadoVoucher e
    on a.NumeroDocumento = e.idEmpregadoVoucher
    join tbTipoVoucher f on e.idTipoVoucher = f.idTipoVoucher
  where c.DataEntrada between '2019-01-23' and '2019-01-27' and (c.idEmpresaAplicativo = 4 or c.idEmpresaAplicativo = 5) 
  and a.idTipoFormaPagto = 1

I tried several ways but as there are many tables Join I am losing myself.

Answer 1

You did not correctly state what the SGBD used, did not inform the structure of the tables and did not say if the types vary or if they are fixed. I used the following structure to MySQL but it will also work for SQL Server whereas the types are fixed:

CREATE TABLE reserva (
  controle   INTEGER,
  quantidade INTEGER,
  tipo       VARCHAR(100),
  valor      NUMERIC(15, 2),
  voucher    VARCHAR(100)
);

INSERT INTO reserva(controle, quantidade, tipo, valor, voucher) VALUES
(114539, 2, 'Associado a partir 12 anos', 158.90, 'Evento'),
(114539, 2, 'Associado de 12 anos', 100.00, 'Promoção'),
(114539, 3, 'Associado de 7 a 11 anos', 100.00, 'Promoção');

The query would look similar to the following:

SELECT r.controle,
       SUM(CASE r.tipo WHEN 'Associado a partir 12 anos' THEN r.quantidade ELSE 0 END) AS 'Associado a partir 12 anos',
       SUM(CASE r.tipo WHEN 'Associado de 12 anos' THEN r.quantidade ELSE 0 END) AS 'Associado de 12 anos',
       SUM(CASE r.tipo WHEN 'Associado de 7 a 11 anos' THEN r.quantidade ELSE 0 END) AS 'Associado de 7 a 11 anos',
       r.valor,
       r.voucher
  FROM reserva r
 GROUP BY r.controle,
          r.valor,
          r.voucher

Resulting in:

| controle | valor | voucher  | Associado a partir 12 anos | Associado de 12 anos | Associado de 7 a 11 anos |
| -------- | ----- | -------- | -------------------------- | -------------------- | ------------------------ |
| 114539   | 158.9 | Evento   | 2                          | 0                    | 0                        |
| 114539   | 100   | Promoção | 0                          | 2                    | 3                        |

You must use in the clause GROUP BY the columns which will group the record, in the case of the result you presented, apparently you just don’t want to group the type (which will become a column using the CASE in query) and the amount (which will be added with the SUM).

You can see the result of query running on the DB Fiddle.

If you want to use the PIVOT to query would look like the following:

SELECT y.controle,
       ISNULL(y.[Associado a partir 12 anos], 0) AS [Associado a partir 12 anos],
       ISNULL(y.[Associado de 12 anos], 0) AS [Associado de 12 anos],
       ISNULL(y.[Associado de 7 a 11 anos], 0) AS [Associado de 7 a 11 anos],
       y.valor,
       y.voucher
  FROM reserva r
 PIVOT (SUM(r.quantidade) FOR r.tipo IN ([Associado a partir 12 anos], [Associado de 12 anos], [Associado de 7 a 11 anos])) y

Using PIVOT and UNPIVOT

You can use relational operators PIVOT and UNPIVOT to change an expression with table value in another table. PIVOT rotates a table value expression by transforming the unique values of a column into multiple columns in the output, and executing aggregations where they are needed at any remaining column values that are desired in the final output. UNPIVOT performs the opposite operation to PIVOT, rotating columns of an expression with table value in column values.