En artículos anteriores habíamos visto como crear tablas agregadas, de una forma muy fácil y sencilla pero  había un pequeño problema que quizás habrás notado: los nombres de las columnas variables aparecían en orden alfabético.

Y eso a veces puede ser lo que quieres y a veces no. Por ejemplo, si las columnas variables se refieren a nombres de productos, de clientes, de países, de alumnos, etc., entonces está bien mostrarlas ordenadas alfabéticamente, pero si se refieren a nombres de meses eso no es lo usual. No se acostumbra ver a los meses así: ABR, AGO, DIC, ENE, FEB, etc. sino que se acostumbra verlos así: ENE, FEB, MAR, ABR, MAY, etc.

Por ese motivo le hice una pequeña modificación al stored procedure CREAR_TABLA_PIVOT el cual ahora permite especificar el orden en el cual se mostrarán las columnas variables. La nueva versión es la siguiente:

CREATE PROCEDURE CREAR_TABLA_PIVOT(
   tcNombreTabla            VARCHAR(  28),
   tcVista                  VARCHAR(  28),
   tcPrimeraColumnaCabecera VARCHAR(  64),
   tcOtrasColumnasCabecera  VARCHAR(4096),
   tcColumnaDatos           VARCHAR(  28),
   tcTipoDatos              VARCHAR(  64),
   tcOrden                  VARCHAR(  28))
AS
   DECLARE VARIABLE lcCreate       VARCHAR(4096);
   DECLARE VARIABLE lcColumna      VARCHAR(  28);
   DECLARE VARIABLE lcMensajeError VARCHAR(1024);
BEGIN

   -- Primero, creamos la tabla

   lcCreate = 'CREATE TABLE ' || tcNombreTabla || ' (';

   lcCreate = lcCreate || tcPrimeraColumnaCabecera || ' NOT NULL, ';

   IF (Char_Length(tcOtrasColumnasCabecera) > 0) THEN BEGIN
      tcOtrasColumnasCabecera = tcOtrasColumnasCabecera || ',';
      WHILE (Char_Length(tcOtrasColumnasCabecera) > 0) DO BEGIN
         EXECUTE PROCEDURE Parser(tcOtrasColumnasCabecera, ',') RETURNING_VALUES :lcColumna;
         lcCreate = lcCreate || Trim(lcColumna) || ' , ' ;
         tcOtrasColumnasCabecera = Replace(tcOtrasColumnasCabecera, lcColumna || ',', '');
      END
   END

   FOR EXECUTE STATEMENT
      'SELECT DISTINCT ' || tcColumnaDatos || ' FROM ' || tcVista || IIF(Char_Length(:tcOrden) > 0, ' ORDER BY ' || :tcOrden, '')
   INTO
      :lcColumna
   DO BEGIN
      lcColumna = Left(lcColumna, 28);
      lcColumna = Replace(lcColumna, ' ', '_');
      lcColumna = Replace(lcColumna, '.', '_');
      lcColumna = Replace(lcColumna, '/', '_');
      lcColumna = Replace(lcColumna, '%', '_');
      lcCreate = lcCreate || lcColumna || ' ' || tcTipoDatos || ', ' ;
   END

   lcCreate = Left(lcCreate, Char_Length(lcCreate) - 2);

   lcCreate = lcCreate || ');';

   EXECUTE STATEMENT lcCreate;

   -- Segundo, le agregamos una Primary Key

   EXECUTE STATEMENT
      'ALTER TABLE ' || tcNombreTabla ||
      ' ADD CONSTRAINT PK_' || tcNombreTabla ||
      ' PRIMARY KEY (' || Left(:tcPrimeraColumnaCabecera, Position(' ', :tcPrimeraColumnaCabecera)) || ')';

END;

donde como puedes ver se agregó el parámetro de entrada tcOrden.

La vista V_VENTAS

CREATE VIEW V_VENTAS(
   MVC_IDECLI,
   MVC_NOMCLI,
   MVC_NUMANO,
   MVC_NUMMES,
   NOMBRE_MES,
   MVC_NOMCLI1,
   MVC_TOTALX)
AS
   SELECT
      MVC_IDECLI,
      C.CLI_NOMBRE AS MVC_NOMCLI,
      EXTRACT(YEAR  FROM MVC_FECHAX) AS MVC_NUMANO,
      EXTRACT(MONTH FROM MVC_FECHAX) AS MVC_NUMMES,
      DECODE(
         EXTRACT(MONTH FROM MVC_FECHAX),
             1, 'ENE',
             2, 'FEB',
             3, 'MAR',
             4, 'ABR',
             5, 'MAY',
             6, 'JUN',
             7, 'JUL',
             8, 'AGO',
             9, 'SEP',
            10, 'OCT',
            11, 'NOV',
            12, 'DIC') AS NOMBRE_MES,
      C.CLI_NOMBRE AS MVC_NOMCLI,
      M.MVC_TOTALX
   FROM
      MOVIMCAB M
   JOIN
      CLIENTES C
         ON M.MVC_IDECLI = C.CLI_IDENTI;

En esta vista tenemos una columna llamada MVC_NUMMES que usaremos para ordenar los nombres de los meses según el número de los meses.

Creando la tabla agregada AGR_VENTAS_MENSUALES

EXECUTE PROCEDURE
   CREAR_TABLA_PIVOT (
      'AGR_VENTAS_MENSUALES',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'INTEGER',
      'MVC_NUMMES')

El último parámetro nos indica el orden en el cual queremos ver a las columnas variables. Si no se especifica ese orden entonces las veremos ordenadas alfabéticamente. Como la columna MVC_NUMMES es de tipo numérico y en ella guardamos los números de los meses (1, 2, 3, 4, 5, etc.) entonces los nombres de los meses estarán ordenados según ese criterio y serán ENE, FEB, MAR, ABR, MAY, etc.

Estructura de la tabla AGR_VENTAS_MENSUALES

AGR1

Captura 1. Si haces clic en la imagen la verás más grande

Insertándole datos a la tabla AGR_VENTAS_MENSUALES

EXECUTE PROCEDURE
   ACTUALIZAR_TABLA_PIVOT (
      'AGR_VENTAS_MENSUALES',
      'V_VENTAS',
      'MVC_NOMCLI VARCHAR(40)',
      '',
      'NOMBRE_MES',
      'SUM(MVC_TOTALX)')

AGR2

Captura 2. Si haces clic en la imagen la verás más grande

Y listo, ya obtuvimos lo que queríamos obtener: los nombres de los clientes y a continuación los nombres de los meses. Si además del nombre del mes también quisiéramos tener el número del año sería muy fácil hacerlo: en la vista V_VENTAS reemplazamos la línea:

12, 'DIC') AS NOMBRE_MES,

por:

12, 'DIC') || '/' || EXTRACT(YEAR FROM MVC_FECHAX) AS NOMBRE_MES,

y después de ejecutar el stored procedure CREAR_TABLA_PIVOT esta será la estructura de nuestra tabla AGR_VENTAS_MENSUALES:

AGR3

Captura 3. Si haces clic en la imagen la verás más grande

Y después de insertarle datos con el stored procedure ACTUALIZAR_TABLA_PIVOT obtendremos esto:

AGR4

Captura 4. Si haces clic en la imagen la verás más grande

Así que ahora el nombre de cada columna variable está compuesto por el nombre del mes y el número del año, generalmente es mejor tenerlas de esta forma para que no se preste a confusión.

Artículos relacionados:

Aplicaciones OLTP y aplicaciones OLAP

Tablas agregadas

Creando tablas dentro de un stored procedure o de un trigger

Creando tablas agregadas e insertándoles datos

Creando y actualizando tablas agregadas de uso general

Otro ejemplo de uso de tablas agregadas

Ventas mensuales a cada cliente usando tablas agregadas

El índice del blog Firebird21