Filas en columnas (que problema!)

Matías,

Algo me llama la atención y es que no veo una clave primaria en esta tabla.
Algo que ayude a identificar unicamente cada fila, pues la combinación
[alumno], [materia] y [IDEtapa] no es unica. Voy a asumir que tienes una
columna con propiedad "identity" que identifica unicamente cada fila.

use northwind
go

create table t1(
pk int not null identity(1, 1) unique,
Alumno varchar(25),
Materia varchar(25),
Nota numeric(5, 2),
IDEtapa int,
Promedio numeric(5, 2)
)
go

set nocount on
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Biologia', 6, 1, 5.5)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Biologia', 5, 1, 5.5)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Biologia', 4, 2, 6)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Biologia', 7, 2, 6)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 8, 1, 7)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 6, 1, 7)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 2, 2, 5)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 9, 2, 5)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 4, 2, 5)
set nocount off
go

create table #t (
Alumno varchar(25),
Materia varchar(25),
N1_1 numeric(5, 2),
N2_1 numeric(5, 2),
N1_2 numeric(5, 2),
N2_2 numeric(5, 2),
N3_2 numeric(5, 2),
Pro_1 numeric(5, 2),
Pro_2 numeric(5, 2)
)

insert into #t(Alumno, Materia, N1_1, N2_1, N1_2, N2_2, N3_2, Pro_1, Pro_2)
select
Alumno,
Materia,
max(N1_1),
max(N2_1),
max(N1_2),
max(N2_2),
max(N3_2),
max(Pro_1),
max(Pro_2)
from
(
select
a.Alumno,
a.Materia,
case when max(a.IDEtapa) = 1 and count(*) = 1 then max(a.Nota) else null
end as N1_1,
case when max(a.IDEtapa) = 1 and count(*) = 2 then max(a.Nota) else null
end as N2_1,
case when max(a.IDEtapa) = 2 and count(*) = 1 then max(a.Nota) else null
end as N1_2,
case when max(a.IDEtapa) = 2 and count(*) = 2 then max(a.Nota) else null
end as N2_2,
case when max(a.IDEtapa) = 2 and count(*) = 3 then max(a.Nota) else null
end as N3_2,
case when max(a.IDEtapa) = 1 and count(*) = 1 then max(a.Promedio) else
null end as Pro_1,
case when max(a.IDEtapa) = 2 and count(*) = 1 then max(a.Promedio) else
null end as Pro_2
from
t1 as a
inner join
t1 as b
on a.Alumno = b.Alumno
and a.Materia = b.Materia
and a.IDEtapa = b.IDEtapa
and a.pk >= b.pk
group by
a.pk, a.Alumno, a.Materia
) as t2
group by
Alumno,
Materia

select *
from #t
order by Alumno, Materia
go

drop table #t
go

drop table t1
go


AMB


"Matías" wrote:

Hola a todos; antes que nada aclaro que ya he leido en distintos articulos y
post anteriores e intentado hacer esto de convertir filas
en columnas(usando cursores y sql dinamico) y no lo he logrado, no pretendo
que me resuelvan el 100% de mi problema, pero necesito una mano para
empezar,
no me importa el metodo para lograrlo(cursores, sql dinamico, tablas temp.,
golpe de estado, lo que sea); estoy con sql2000.

Tengo el resultado de la consulta(con columnas resumidas):

Alumno Materia Nota IDEtapa Promedio
JUAN Biologia 6 1 5.5
JUAN Biologia 5 1 5.5
JUAN Biologia 4 2 6
JUAN Biologia 7 2 6
JUAN Historia 8 1 7
JUAN Historia 6 1 7
JUAN Historia 2 2 5
JUAN Historia 9 2 5
JUAN Historia 4 2 5

Y me hace falta de la siguiente forma:

Alumno Materia N1_1 N2_1 N1_2 N2_2 N3_2 Pro_1 Pro_2
JUAN Biologia 6 5 4 7 5.5
6
JUAN Historia 8 6 2 9 4 7
5

donde por ej; N1_1 significa Nota 1 de la etapa 1, N1_2 Nota1 de la etapa 2.

SELECT A.Apellidos+', '+A.Nombres as Alumno, A.Legajo, M.Nombre as Materia,
N1.Nota, N1.IDEtapa, E.Descripcion as Etapa,
N1.Curso,
Promedio = (SELECT ROUND(AVG(CAST(N2.Nota as FLOAT)),2)
FROM NOTAS N2
WHERE N2.IDEtapa = N1.IDEtapa AND N2.IDMateria = N1.IDMateria AND
N2.IdAlumno = N1.IdAlumno AND DATEPART(yyyy, N2.FechaExamen) = @Anio)
FROM NOTAS N1
INNER JOIN Materias M ON N1.IDMateria = M.IDMateria
INNER JOIN Alumnos A ON N1.IDAlumno = A.IDAlumno
INNER JOIN Etapas E ON N1.IDEtapa = E.IDEtapa
WHERE N1.IdAlumno = @IdAlumno AND N1.IDEtapa = 1 AND DATEPART(yyyy,
N1.FechaExamen) = @Anio
UNION
SELECT A.Apellidos+', '+A.Nombres as Alumno, A.Legajo, M.Nombre as Materia,
N1.Nota, N1.IDEtapa, E.Descripcion as Etapa,
N1.Curso,
Promedio = (SELECT ROUND(AVG(CAST(N2.Nota as FLOAT)),2)
FROM NOTAS N2
WHERE N2.IDEtapa = N1.IDEtapa AND N2.IDMateria = N1.IDMateria AND
N2.IdAlumno = N1.IdAlumno AND DATEPART(yyyy, N2.FechaExamen) = @Anio)
FROM NOTAS N1
INNER JOIN Materias M ON N1.IDMateria = M.IDMateria
INNER JOIN Alumnos A ON N1.IDAlumno = A.IDAlumno
INNER JOIN Etapas E ON N1.IDEtapa = E.IDEtapa
WHERE N1.IdAlumno = @IdAlumno AND N1.IDEtapa = 2 AND DATEPART(yyyy,
N1.FechaExamen) = @Anio

Muchas gracias por su tiempo.

PD: por las dudas coloco el script de la tabla NOTAS
CREATE TABLE [dbo].[Notas] (
[IDAlumno] [int] NOT NULL ,
[IDMateria] [smallint] NOT NULL ,
[IDEtapa] [smallint] NOT NULL ,
[Curso] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Orden] [int] IDENTITY (1, 1) NOT NULL ,
[Nota] [smallint] NOT NULL ,
[FechaExamen] [smalldatetime] NULL
) ON [PRIMARY]

ok, gracias, te comento que los campos claves de la tabla Notas son:
IDAlumno
IDMateria
IDEtapa
Curso
Orden (autonumerico)

Alejandro una preguntita, que significa las sentencia "and count(*) = 3" de:

case when max(a.IDEtapa) = 2 and count(*) = 3 then max(a.Nota) else null
end as N3_2

veo que vas variando el numero "count(*) = x"de acuerdo a la columna Nx_x,
pero cual es el patron para esto?

te pregunto porque tengo que agregar mas columnas, ya que no conozco la
cantidad de notas que puede tener un alumno, algo asi:

create table #t (
Alumno varchar(25),
Materia varchar(25),
N1_1 numeric(5, 2),
N2_1 numeric(5, 2),
N3_1 numeric(5, 2),
N4_1 numeric(5, 2),
N5_1 numeric(5, 2),
N1_2 numeric(5, 2),
N2_2 numeric(5, 2),
N3_2 numeric(5, 2),
N4_2 numeric(5, 2),
N5_2 numeric(5, 2)
)

Saludos y mil gracias por tu valioso tiempo.

Matías,

Para poder saber la secuencia de cada fila dentro del grupo (pk, alumno,
materia, idetapa). Algo asi como:

pk alumno materia idetapa secuencia
1 JUAN Biologia 1 1 <-- N1_1
2 JUAN Biologia 1 2 <-- N2_1
3 JUAN Biologia 2 1 <-- N1_2
4 JUAN Biologia 2 2
5 JUAN Historia 1 1
6 JUAN Historia 1 2
7 JUAN Historia 2 1
8 JUAN Historia 2 2
9 JUAN Historia 2 3 <-- N3_2

Lo cual puedes ver si usas:

select
a.pk,
a.Alumno,
a.Materia,
a.IDEtapa,
count(*) as secuencia
from
t1 as a
inner join
t1 as b
on a.Alumno = b.Alumno
and a.Materia = b.Materia
and a.IDEtapa = b.IDEtapa
and a.pk >= b.pk
group by
a.pk,
a.Alumno,
a.Materia,
a.IDEtapa

Claro que lo complique un poquito (no me di cuenta) al usar la funcion de
grupo max(idetapa), en vez de agrupar tambien por esa columna. fijate que la
expresion "count(*) = 1" equivale a preguntar "secuencia = 1". La forma
correcta es:

select
a.Alumno,
a.Materia,
case when a.IDEtapa = 1 and count(*) = 1 then max(a.Nota) else null end as
N1_1,
case when a.IDEtapa = 1 and count(*) = 2 then max(a.Nota) else null end as
N2_1,
case when a.IDEtapa = 2 and count(*) = 1 then max(a.Nota) else null end as
N1_2,
case when a.IDEtapa = 2 and count(*) = 2 then max(a.Nota) else null end as
N2_2,
case when a.IDEtapa = 2 and count(*) = 3 then max(a.Nota) else null end as
N3_2,
case when a.IDEtapa = 1 and count(*) = 1 then max(a.Promedio) else null end
as Pro_1,
case when a.IDEtapa = 2 and count(*) = 1 then max(a.Promedio) else null end
as Pro_2
from
t1 as a
inner join
t1 as b
on a.Alumno = b.Alumno
and a.Materia = b.Materia
and a.IDEtapa = b.IDEtapa
and a.pk >= b.pk
group by
a.pk, a.Alumno, a.Materia, a.IDEtapa

Aca te paso el script completo, ya modificado.

use northwind
go

create table t1(
pk int not null identity(1, 1) unique,
Alumno varchar(25),
Materia varchar(25),
Nota numeric(5, 2),
IDEtapa int,
Promedio numeric(5, 2)
)
go

set nocount on
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Biologia', 6, 1, 5.5)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Biologia', 5, 1, 5.5)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Biologia', 4, 2, 6)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Biologia', 7, 2, 6)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 8, 1, 7)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 6, 1, 7)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 2, 2, 5)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 9, 2, 5)
insert into t1 (Alumno, Materia, Nota, IDEtapa, Promedio) values('JUAN',
'Historia', 4, 2, 5)
set nocount off
go

create table #t (
Alumno varchar(25),
Materia varchar(25),
N1_1 numeric(5, 2),
N2_1 numeric(5, 2),
N1_2 numeric(5, 2),
N2_2 numeric(5, 2),
N3_2 numeric(5, 2),
Pro_1 numeric(5, 2),
Pro_2 numeric(5, 2)
)

insert into #t(Alumno, Materia, N1_1, N2_1, N1_2, N2_2, N3_2, Pro_1, Pro_2)
select
Alumno,
Materia,
max(N1_1),
max(N2_1),
max(N1_2),
max(N2_2),
max(N3_2),
max(Pro_1),
max(Pro_2)
from
(
select
a.Alumno,
a.Materia,
case when a.IDEtapa = 1 and count(*) = 1 then max(a.Nota) else null end as
N1_1,
case when a.IDEtapa = 1 and count(*) = 2 then max(a.Nota) else null end as
N2_1,
case when a.IDEtapa = 2 and count(*) = 1 then max(a.Nota) else null end as
N1_2,
case when a.IDEtapa = 2 and count(*) = 2 then max(a.Nota) else null end as
N2_2,
case when a.IDEtapa = 2 and count(*) = 3 then max(a.Nota) else null end as
N3_2,
case when a.IDEtapa = 1 and count(*) = 1 then max(a.Promedio) else null end
as Pro_1,
case when a.IDEtapa = 2 and count(*) = 1 then max(a.Promedio) else null end
as Pro_2
from
t1 as a
inner join
t1 as b
on a.Alumno = b.Alumno
and a.Materia = b.Materia
and a.IDEtapa = b.IDEtapa
and a.pk >= b.pk
group by
a.pk, a.Alumno, a.Materia, a.IDEtapa
) as t2
group by
Alumno,
Materia

select *
from #t
order by Alumno, Materia
go

drop table #t
go

drop table t1
go


AMB


"Matías" wrote:

Alejandro una preguntita, que significa las sentencia "and count(*) = 3" de:

case when max(a.IDEtapa) = 2 and count(*) = 3 then max(a.Nota) else null
end as N3_2

veo que vas variando el numero "count(*) = x"de acuerdo a la columna Nx_x,
pero cual es el patron para esto?

te pregunto porque tengo que agregar mas columnas, ya que no conozco la
cantidad de notas que puede tener un alumno, algo asi:

create table #t (
Alumno varchar(25),
Materia varchar(25),
N1_1 numeric(5, 2),
N2_1 numeric(5, 2),
N3_1 numeric(5, 2),
N4_1 numeric(5, 2),
N5_1 numeric(5, 2),
N1_2 numeric(5, 2),
N2_2 numeric(5, 2),
N3_2 numeric(5, 2),
N4_2 numeric(5, 2),
N5_2 numeric(5, 2)
)

Saludos y mil gracias por tu valioso tiempo.

OK, muchas gracias, te comento que tu consulta me sirve como punto de
partida, ya que tengo unos cuantos sp's que tengo que pasar filas a
columnas.
Saludos y 1.000.000 de gracias