1
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.
IS
MySQL
orSQL Server
? Are the values fixed or variable? You did not add the structure of the tables and gave little information to enable the answer. Try to simplify your question so that it is easy to find the root of your problem and propose a solution. The way your question was asked is difficult to make the answer useful to other users– Sorack
I use 'SQL SERVER', the values are fixed, my biggest problem would be to turn these lines "like hosting" into columns avoiding that create several rows because of the Number of people and the type of hosting, 1 room can have several types of guests and I would like to leave everything together the same accommodation and voucher. Obs: Sorack you’re the short guy your sql tramp here in the stack learned a lot from 0 with vc helping the xD crowd,
– Axcse
@Axcse In the article "Alas & Pivots" you find step by step how to assemble the various types of pivot. Log in: https://portosql.wordpress.com/2019/05/04/pivot/
– José Diz