PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Tuesday, November 1, 2022

[FIXED] How to use PIVOT

 November 01, 2022     pivot, pivot-table, sql, sql-server     No comments   

Issue

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!!!


Solution

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



Answered By - DannySlor
Answer Checked By - Clifford M. (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing