none
Utilizar SELECT como cabecera

    Pregunta

  • Hola,

    Me ha surgido una duda...supongamos que tengo una tabla con las notas de los alumnos en distintos examenes de asignaturas diferentes. Tendría las siguientes tablas:

    - ALUMNO: dni, nombre

    - EXAMEN: id_examen, asignatura, nombre

    - NOTAS: id_examen, dni, nota

    Querría una consulta que me saque las notas de todos los alumnos de la clase por asignatura, es decir una tabla que tuviera lo siguiente:

    DNI            - MATEMATICAS - LENGUA - CONOCIMIENTO DEL MEDIO - INGLES

    50111111p            6                   7                               8,5                       9

    50999999y            2                   3                                  9                       1

     

    No se si entendéis la problemática...quería poner como cabecera de la tabla resultante el resultado de una select que me saque las asignaturas que hay...

    Muchas gracias!!!

    viernes, 28 de mayo de 2010 18:19

Respuestas

  • Hola,

    Pues la verdad es que al final lo que hice fue crearme un datatable a mano e ir introduciendo las columas que necesitaba a medida que las iba sacando de la base de datos con distintas select...

    De todas formas muchas gracias por vuestra ayuda!

    viernes, 04 de junio de 2010 17:05

Todas las respuestas

  • Si tienes SQL Server 2005 o posterior, lo puedes conseguir mediante la sentencia PIVOT.

    http://msdn.microsoft.com/es-es/library/ms177410(SQL.90).aspx

     

    viernes, 28 de mayo de 2010 18:31
  • Hola.

    Sería más o menos así:

    select dni, 
    	matematicas = isnull(max(case when asignatura = 'Matematicas' then nota else 0 end), 0), 
    	lengua = isnull(max(case when asignatura = 'Lengua' then nota else 0 end), 0), 
    	--Resto de asignaturas
    from alumno A left join notas N on A.dni = N.dni inner join examen E on N.id_examen = E.id_examen
    group by dni
    

     


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    viernes, 28 de mayo de 2010 18:32
    Moderador
  • El problema es que no se las asignaturas que hay...no puedo saberlo porque esto lo voy a utilizar en codigo...entonces tambien tendria que hacer una select de las asignaturas que tienen los examenes...no puedo poner "matematicas"...
    viernes, 28 de mayo de 2010 18:40
  • Pues entonces es bastante más dificil. Tanto el PIVOT que yo te proponía como la lista con CASE que te propone qwalgrande requieren conocer la lista de posibles valores para las columnas. Si la tienes que sacar con un Select, necesitarás usar un cursor para construir la lista a partir de la select, y luego concatenarla con el resto de la consulta y ejecutarla como Sql dinámico (sp_executesql). En cualquier caso te va a quedar bastante complicado, pero dentro de lo que cabe será más simple construir la lista de columnas para el PIVOT que una larga lista de sentencias con CASE.

     

    viernes, 28 de mayo de 2010 18:55
  • Si lo quieres para un informe no tendrías que complicarte tanto la vida, simplemente creas una consulta de tipo :

    DNI, Asignatura, Nota

    Y luego en el Report Viewer creas un control de tipo Matrix y el lo hace por tí.

    Si es para un GridView, ahi si deberías usar o la sentencia PIVOT o el método que te dice Alberto.

     

    Cordial saludo,

     


    Mauricio Atanache G. - MCP
    Bogotá - Colombia
    "Bienaventurados los Pesimistas. Por que hacen BACKUPS."
    viernes, 28 de mayo de 2010 19:09
  • Hola.

    Aunque no las conozcas, tampoco son infinitas. Consulta la tabla que hay para hacer la consulta a medida. Después, una consulta con un número variable de columnas... Eso es SQL dinámico y no lo puedo recomendar para un caso como éste si no es dentro de un procedimiento almacenado.

    En cualquier caso, creo que es un salto demasiado grande. Podrías encontrar aquí el código, copiarlo y pegarlo y nada aprenderías. Insisto en que es mejor dar la consulta con los valores de las asignaturas prefijadas. A lo mejor te toca hacer una consulta para cada curso, pero tampoco sería ni mucho menos tan complejo como construir una sentencia pivot dinámica en un procedimiento almacenado.

    En cualquier caso, si no lo logras, nos dices.

     


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    viernes, 28 de mayo de 2010 19:11
    Moderador
  • OK, aqui tienes la solución, con PIVOT y un cursor:

    USE Prueba
    GO
    CREATE TABLE Alumno (
      Dni varchar(10),
      nombre varchar(50)
    )
    GO
    CREATE TABLE Examen (
      id_examen int,
      asignatura varchar(50)
    )
    GO
    CREATE TABLE Notas (
      id_examen int,
      Dni varchar(10),
      nota float
    )
    GO
    Insert Alumno values('50111111p', 'Fulano de Tal')
    Insert Alumno values('50999999y', 'Mengano de Cual')
    GO
    Insert Examen values(1, 'MATEMATICAS')
    Insert Examen values(2, 'LENGUA')
    Insert Examen values(3, 'CONOCIMIENTO DEL MEDIO')
    Insert Examen values(4, 'INGLÉS')
    GO
    Insert Notas values(1, '50111111p', 6)
    Insert Notas values(2, '50111111p', 7)
    Insert Notas values(3, '50111111p', 8.5)
    Insert Notas values(4, '50111111p', 9)
    Insert Notas values(1, '50999999y', 2)
    Insert Notas values(2, '50999999y', 3)
    Insert Notas values(3, '50999999y', 9)
    Insert Notas values(4, '50999999y', 1)
    GO
    Declare @asignatura varchar(50)
    declare @lista varchar(4000)
    Declare c Cursor for Select asignatura from Examen
    open c
    fetch next from c into @asignatura
    set @lista=''
    WHILE @@FETCH_STATUS = 0
    BEGIN
     Set @lista = @lista+'['+@asignatura+']'
     fetch next from c into @asignatura
     if @@FETCH_STATUS = 0 set @lista=@lista+','
    END
    close c
    deallocate c
    declare @sentencia nvarchar(4000)
    set @sentencia='
    With Datos As (
    Select Dni, asignatura, nota from Notas n
      Left Join Examen e on e.id_examen = n.id_examen) 
    Select * from Datos
      PIVOT (SUM(nota) FOR asignatura in ('+@lista+')) PVT'
    EXECUTE sp_executesql @sentencia
    viernes, 28 de mayo de 2010 19:24
  • Vale...voy a ver si con esto último consigo adecuarlo a mi caso...porque os he puesto un ejemplo parecido a lo que yo tengo pero mas sencillo...realmente no puedo saber las asignaturas, ya que es para una aplicación web en la que todo es muy "libre" se diseñan examenes y es un poco a gusto del usuario, por lo que nunca podré saber lo que va a decidir meter...

    Muchas gracias por todas las respuestas...voy a probar esto ultimo entonces a ver si me funciona bien y os comento...

    viernes, 28 de mayo de 2010 22:27
  • Hola.

    ¿Pudiste implementarlo?


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    viernes, 04 de junio de 2010 13:44
    Moderador
  • Hola,

    Pues la verdad es que al final lo que hice fue crearme un datatable a mano e ir introduciendo las columas que necesitaba a medida que las iba sacando de la base de datos con distintas select...

    De todas formas muchas gracias por vuestra ayuda!

    viernes, 04 de junio de 2010 17:05
  • Aqui tienes otra opcion, que es crear la lista de asignaturas usando la clausula FOR XML PATH, en vez de usar un cursor.

    USE tempdb;
    GO
    CREATE TABLE Alumno (
     Dni varchar(10),
     nombre varchar(50)
    )
    GO
    CREATE TABLE Examen (
     id_examen int,
     asignatura varchar(50)
    )
    GO
    CREATE TABLE Notas (
     id_examen int,
     Dni varchar(10),
     nota float
    )
    GO
    Insert Alumno values('50111111p', 'Fulano de Tal')
    Insert Alumno values('50999999y', 'Mengano de Cual')
    GO
    Insert Examen values(1, 'MATEMATICAS')
    Insert Examen values(2, 'LENGUA')
    Insert Examen values(3, 'CONOCIMIENTO DEL MEDIO')
    Insert Examen values(4, 'INGLÉS')
    GO
    Insert Notas values(1, '50111111p', 6)
    Insert Notas values(2, '50111111p', 7)
    Insert Notas values(3, '50111111p', 8.5)
    Insert Notas values(4, '50111111p', 9)
    Insert Notas values(1, '50999999y', 2)
    Insert Notas values(2, '50999999y', 3)
    Insert Notas values(3, '50999999y', 9)
    Insert Notas values(4, '50999999y', 1)
    GO
    DECLARE @asignaturas nvarchar(MAX);
    DECLARE @sql nvarchar(MAX);
    
    SET @asignaturas = STUFF(
    (
    SELECT CAST(',' AS nvarchar(MAX)) + QUOTENAME(asignatura)
    FROM (SELECT DISTINCT asignatura FROM Examen) AS E
    ORDER BY asignatura
    FOR XML PATH('')
    ), 1, 1, '');
    
    set @sql = N'
    WITH r_set AS (
    SELECT
    	A.Dni,
    	E.asignatura,
    	N.nota
    FROM
    	Notas AS N
    	INNER JOIN
    	Alumno AS A
    	ON N.Dni = A.Dni
    	INNER JOIN
    	Examen AS E
    	ON N.id_examen = E.id_examen
    )
    SELECT
    	PVT.*
    FROM
    	r_set
    	PIVOT
    	(
    	MIN(nota)
    	FOR asignatura IN (' + @asignaturas + N')) AS PVT';
    
    EXEC sp_executesql @sql;
    GO
    DROP TABLE alumno, examen, notas;
    GO

    Debes tener cuidado al implementar pivot dinamico, pues al igual que cualquier otra sentencia dinamica, se abre da la posibilidad para injectar codigo sql. Una forma de hacer mas dificil la injeccion de codigo, es usando la funcion QUOTENAME para encerrar las asignaturas como identificadores.

    Gracias a Alberto Poblacion por proveer el esquema de las tablas y data de ejemplo.

    AMB

    viernes, 04 de junio de 2010 17:25