Post reply

Cross Tab Access to SQL Server

  • September 9, 2005 at 3:48 pm

    #69638

    Hi!, I'm a new in SQL Server; I Have a crooss tab Query in Access

    PARAMETERS fPeriodo Long, fPlan Text ( 255 );

    SELECT IIf(FolioPeriodo=[fPeriodo],FolioCursoImpartido,Null) AS fFolioCursoImpartido, IIf(FolioPeriodo=[fPeriodo],AlumnosInscritos,Null) AS fAlumnosInscritos, Carreras.*, GradosPorCarrera.*

    FROM (Carreras INNER JOIN GradosPorCarrera ON Carreras.FolioCarrera = GradosPorCarrera.FolioCarrera) LEFT JOIN CursosImpartidos ON GradosPorCarrera.FolioGrado = CursosImpartidos.FolioGrado

    WHERE (((Carreras.PlanDeEstudios)=[fPlan]));

     

    TRANSFORM Format(Count(fFolioCursoImpartido),'00;; ')+Format(Sum(fAlumnosInscritos),'(00);; ') AS numGrupos

    SELECT CarrerasGradosQuery.Carreras.FolioCarrera, CarrerasGradosQuery.Carreras.Descripcion, CarrerasGradosQuery.Carreras.PlanDeEstudios AS Expr1

    FROM CarrerasGradosQuery

    GROUP BY CarrerasGradosQuery.Carreras.FolioCarrera, CarrerasGradosQuery.Carreras.Descripcion, CarrerasGradosQuery.Carreras.PlanDeEstudios

    PIVOT GradosPorCarrera.Grado;

    How I  could do It In SQL, I was using a store procedure but the command EXEC did´t do nothing. maybe the user can´t do it for the security restrictions.

    Thenks alot

  • September 9, 2005 at 5:28 pm

    #588941

    This is complicated.  Here are some general guidelines: 

    For simple crosstabs, you can use a pseudo-Pivot table.  Search for Pivot Tables and CASE functions in BOL for more info.  There is no Pivot or transform function in SQL Server 2000, unfortunately.

    SQL Server 2005 has some new built-in pivot functions.

    You can also connect to SQL Server with ODBC in an MDB, and use JET SQL to create the Pivot Table.

    For very complex crosstabs you may have to roll your own, by creating a new table and stuffing it with transformed data.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply