how to transpose columns and show percentage? SQL server

Question

how to transpose columns and show percentage? SQL server

Navigation

#1 by (1 votes)

0

This is my code:

select  ROW_NUMBER() OVER (ORDER BY p.CodPersona) AS Numero,
    ccc.Descripcion     AS      Curso,
    p.CodPersona        AS      Codigo,
    p.NombreCompleto    AS      NombreAlumno,
    cc.Detalle          AS      Detalle,
    SUM(cc.Valor)       AS      Valor

    from CuentaCorriente cc
    inner join AlumnosBD.dbo.Alumno a on cc.IdAlumno = a.CodAlumno
    inner join AlumnosBD.dbo.Matricula m on cc.IdAlumno = m.CodAlumno
    inner join AlumnosBD.dbo.Curso ccc on m.CodCurso = ccc.CodCurso
    inner join AlumnosBD.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
    order by Codigo

It shows me like this:

I want you to show me this way

sql sql-server

asked by Rodrigo Rodriguez 25.09.2017 в 18:57

source

1 answer

Divide data from different tables in sql setBackgroundresource in an ImageButton

score 1 · Accepted Answer

Recover separately the discounts that a student may have in a course (I understand it can be one or more than one) and then combine with the rows that have the concept 'Monthly', for example:

WITH Descuentos AS
(
SELECT
   ccc.Descripcion AS Curso, p.CodPersona AS Codigo,       
   cc.Detalle AS Tipo_Descuento, cc.Valor AS Valor
FROM 
   CuentaCorriente cc
   INNER JOIN AlumnosBD.dbo.Alumno a ON cc.IdAlumno = a.CodAlumno
   INNER JOIN AlumnosBD.dbo.Matricula m ON cc.IdAlumno = m.CodAlumno
   INNER JOIN AlumnosBD.dbo.Curso ccc ON m.CodCurso = ccc.CodCurso
   INNER JOIN AlumnosBD.dbo.Persona p ON a.CodPersona = p.CodPersona
WHERE cc.Detalle LIKE 'Descuento%' AND cc.Valor != 0.00    
)
SELECT
    ROW_NUMBER() OVER(ORDER BY p.CodPersona) AS Numero,
    ccc.Descripcion AS Curso,
    p.CodPersona AS Codigo,
    p.NombreCompleto AS NombreAlumno,
    d.Tipo_Descuento AS Tipo_Descuento,
    cc.Valor AS Valor,
    COALESCE(d.Valor, 0) AS Valor_Descuento,
    COALESCE(d.Valor, 0) * 100 / cc.Valor AS Porcentaje
FROM 
    CuentaCorriente cc
    INNER JOIN AlumnosBD.dbo.Alumno a ON cc.IdAlumno = a.CodAlumno
    INNER JOIN AlumnosBD.dbo.Matricula m ON cc.IdAlumno = m.CodAlumno
    INNER JOIN AlumnosBD.dbo.Curso ccc ON m.CodCurso = ccc.CodCurso
    INNER JOIN AlumnosBD.dbo.Persona p ON a.CodPersona = p.CodPersona
    LEFT JOIN Descuentos d ON ccc.Descripcion = d.Curso 
        AND p.CodPersona = d.Codigo
WHERE cc.Detalle = 'Mensualidad' AND cc.Valor != 0.00
ORDER BY Codigo;
GO