how to transpose columns and hide row?

Question

how to transpose columns and hide row?

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

0

the code I'm using:

select  
    ccc.Descripcion AS Grado,
    p.CodPersona AS Codigo,
    p.NombreCompleto AS Nombre,
    cc.Detalle AS Detalle,
    SUM(cc.Valor) AS Valor

from Cuenta cc
        inner join AlumnoDB.dbo.Alumno a on cc.IdAlumno = a.CodAlumno
        inner join AlumnoDB.dbo.Matricula m on cc.IdAlumno = m.CodAlumno
        inner join AlumnoDB.dbo.Curso ccc on m.CodCurso = ccc.CodCurso
        inner join AlumnoDB.dbo.Persona p on a.CodPersona = p.CodPersona

        where cc.Detalle is not null
        and cc.Valor != 0.00

        group by cc.Detalle, 
            ccc.Descripcion, 
            p.CodPersona,
            p.NombreCompleto

Show me this result:

that the resgitros become columas EYE: (that result the grouping of 12 records, and there will only be 3 (Discount, monthly fee and record) types of details for N records) I want that Discount and monthly payment are as column and invoice left hidden.

now how can I show it like this:

mysql sql sql-server query

asked by Rodrigo Rodriguez 25.09.2017 в 16:49

source

3 answers

2

It is not necessary to make any transposition, simply with a% conditional% co_ should reach you, let's see:

select  ccc.Descripcion AS Grado,
        p.CodPersona AS Codigo,
        p.NombreCompleto AS Nombre,
        SUM(CASE WHEN Detalle = 'Descuento' THEN Valor ELSE O END) AS 'Descuento',
        SUM(CASE WHEN Detalle = 'Mensualidad' THEN Valor ELSE O END) AS 'Mensualidad'
        from Cuenta cc
        inner join AlumnoDB.dbo.Alumno a on cc.IdAlumno = a.CodAlumno
        inner join AlumnoDB.dbo.Matricula m on cc.IdAlumno = m.CodAlumno
        inner join AlumnoDB.dbo.Curso ccc on m.CodCurso = ccc.CodCurso
        inner join AlumnoDB.dbo.Persona p on a.CodPersona = p.CodPersona
        where cc.Detalle is not null
            and cc.Valor != 0.00
        group by ccc.Descripcion, 
                 p.CodPersona,
                 p.NombreCompleto

answered by 25.09.2017 в 17:17

0

What I am going to do is going to solve your problem, but I can not guarantee that it is optimal or anything like that:

select  
  ccc.Descripcion AS Grado,
  p.CodPersona AS Codigo,
  p.NombreCompleto AS Nombre,
  (SELECT SUM(cc.Valor) FROM Cuenta WHERE cc.Detalle = "DESCUENTO" and ??? GROUP BY ???) as DESCUENTO
  (SELECT SUM(cc.Valor) FROM Cuenta WHERE cc.Detalle = "MENSUALIDAD" and ??? GROUP BY ???) as MENSUALIDAD
from Cuenta cc INNER JOIN .....

Notice that it is easy to put together a column for each of the data you need. fill in the querys with ??? because not knowing your exact model, I do not know what other conditions you need, not even if a group by is necessary (pq depending on the conditions you may already get the direct sum).

answered by 25.09.2017 в 17:15

Member of the class "was not declared in this scope" ORACLE 11g Execute Trigger

score 0 · Accepted Answer

select  
    ccc.Descripcion     AS      Grado,
    p.CodPersona        AS      Codigo,
    p.NombreCompleto    AS      Nombre,
    SUM(CASE WHEN cc.Detalle = 'Mensualidad' THEN cc.Valor END) AS [Mensualidad],
    SUM(CASE WHEN cc.Detalle = 'Descuento' THEN cc.Valor END)*-1 AS [Descuento]

from Cuenta as cc
    inner join AlumnoDB.dbo.Alumno a on cc.IdAlumno = a.CodAlumno
    inner join AlumnoDB.dbo.Matricula m on cc.IdAlumno = m.CodAlumno
    inner join AlumnoDB.dbo.Curso ccc on m.CodCurso = ccc.CodCurso
    inner join AlumnoDB.dbo.Persona p on a.CodPersona = p.CodPersona

    WHERE cc.Detalle IS NOT NULL
            AND cc.Valor != 0.00
            AND cc.Detalle IN ( 'Mensualidad','Descuento')

    group by ccc.Descripcion, 
        p.CodPersona,
        p.NombreCompleto;