How can I transpose column transpose columns and hide rows and hide row?

Question

0.00/5 (No votes)

See more:

SQL

SQL-Server

SqlDataSource

, +

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 25-Sep-17 4:57am

Rodrigo Alex Rodriguez

Updated 25-Sep-17 6:23am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bryian Tan · Accepted Answer · 2017-09-25T06:23:00

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