Click here to Skip to main content
15,901,035 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
this is my code:

SQL
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


this is result:

Grado    Codigo  Nombre   Detalle        Valor
Tercero	  4664    Angie	  Descuento     -250.00
Tercero	  4664	  Angie	  Mensualidad	5000.00
Tercero	  4664    Angie	  Fact	        -875.00



(that result the grouping of 12 records, and there will only be 3 (Discount, monthly and registration) types of details for N records) I want that Discount and monthly payment remain as column and invoice is hidden.

now as I show it:

Grado     Codigo   nombre   Descuento  Mensualidad 
Tercero   4664	   Angie    250.00     5000.00


What I have tried:

<pre lang="SQL">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
Posted
Updated 25-Sep-17 6:23am

1 solution

You can use PIVOT function. Here is an example SQL PIVOT Rows to Columns - Stack Exchange Data Explorer[^]

SQL
DECLARE @test TABLE (
    Grado VARCHAR(50),
    Codigo VARCHAR(50),
    Nombre VARCHAR(50),
    Detalle VARCHAR(50),
    Valor VARCHAR(50)
)

INSERT INTO @test
    SELECT 'Tercero','4664','Angie','Descuento','-250.00'
    UNION
    SELECT 'Tercero','4664','Angie','Mensualidad','5000.00'
    UNION
    SELECT 'Tercero','4664','Angie','Fact','-875.00';

--SELECT Detalle,Codigo, Valor FROM @test

select Grado, Codigo, Nombre, Descuento, Mensualidad
from
(
  select Grado, Codigo,Nombre, Valor, Detalle
  from @test
) d
pivot
(
  max(Valor)
  for Detalle in (Descuento, Mensualidad)
) piv;


Output:
Grado	Codigo	Nombre	Descuento	Mensualidad
Tercero	4664	Angie	-250.00	        5000.00
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900