Home > Blockchain >  How to use PIVOT
How to use PIVOT

Time:10-29

can you help me please?

I have this:

| NCuota | Cuenta  | Capital|Interes | IVA | Seguro| Comisión | ImpuestoComis | VCuota|
| 1      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 2      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 3      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 4      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 5      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 6      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 7      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 8      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 9      | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 10     | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 11     | 123     |    100 |50      |5    |3      |1         |1              |160    |
| 12     | 123     |    100 |50      |5    |3      |1         |1              |160    |

With this query:

select 
ROW_NUMBER ( ) OVER (ORDER BY a.PPFPAG ASC) as NCuota, ppcta as Cuenta, 
a.ppcap as Capital, a.ppint as Interes, a.ppiint as IVA, ppimp11 as Seguro, e.Pp002Imp as Comision, e.Pp002Aux1 as ImpuestoComision,
(a.ppcap   a.ppint   a.ppiint   ppimp11   e.Pp002Imp   Pp002Aux1) as Vcuota
From fsd601 a (nolock), fsd611 b (nolock), fsd010 c (nolock), fsr008 d (nolock), fpp002 e (nolock), x054023 f (nolock) 
where a.pgcod=b.pgcod and a.ppmod=b.ppmod and a.ppsuc=b.ppsuc and a.ppmda=b.ppmda and a.pppap=b.pppap 
and a.ppcta=b.ppcta  and a.ppoper=b.ppoper  and a.ppsbop=b.ppsbop  and a.pptope=b.pptope and a.ppfpag=b.ppfpag 
and a.pgcod=c.pgcod and a.ppmod=c.aomod and a.ppsuc=c.aosuc and a.ppmda=c.aomda and a.pppap=c.aopap 
and a.ppcta=c.aocta and a.ppoper=c.aooper and a.ppsbop=c.aosbop and a.pptope=c.aotope and c.aostat=0 
and c.aocta=d.ctnro and d.petdoc in (1,3) and cttfir='T' and a.ppmod=e.ppmod and a.ppsuc=e.ppsuc and a.ppmda=e.ppmda
and a.pppap=e.pppap and a.ppcta=e.ppcta and a.ppoper=e.ppoper and a.ppsbop=e.ppsbop and a.pptope=e.pptope 
and a.ppfpag=e.ppfpag and e.PrestConc=6 and a.ppmod=f.xllaomod and a.ppsuc=f.xllaosuc and a.ppmda=f.xllaomda 
and a.pppap=f.xllaopap and a.ppcta=f.xllaocta and a.ppoper=f.xllaooper and a.ppsbop=f.xllaosbop and a.pptope=f.xllaotop 
and c.aocta=63658 and c.aooper=333718  

But i really need this and i don't know how :/

| Cuenta | 1  | 2  | 3  | 4  | 5  | 6  | 7  | 8  | 9  | 10 | 11 | 12 |
| 123    |160 |160 |160 |160 |160 |160 |160 |160 |160 |160 |160 |160 |  

Thank You!!!

CodePudding user response:

select  Cuenta
       ,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
    
from    t
pivot  (max(VCuota) for NCuota in([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p
Cuenta 1 2 3 4 5 6 7 8 9 10 11 12
123 160 160 160 160 160 160 160 160 160 160 160 160

Fiddle

  • Related