Procedimiento que regresa una tabla cruzada usando Db2

Procedimiento que regresa una tabla cruzada usando Db2

tabla_cruzada

Sharing is caring!

Concepto básico de tabla cruzada usando Db2

El concepto básico de una tabla cruzada es que los valores de fila se transforman en valores de columna. A menudo he deseado que DB2 tuviera un procedimiento o función que nos regrese una tabla pivote. Pero hasta que llegue ese día, tendré que arreglármelas con una solución alternativa que también podría serle útil.

La solución alternativa tiene la forma de un procedimiento almacenado que puede usarse para pivotar cualquier columna en cualquier tabla. Antes de llegar al procedimiento almacenado, veamos un ejemplo pivote simple.

tabla_cruzada, tabla cruzada usando db2, pivot table in db2,

Estos ejemplos están utilizando la tabla SALES en la base de datos de muestra de DB2. Para crear la base de datos de ejemplo de DB2 en su sistema, llame al procedimiento almacenado de SQL

CALL QSYS/CREATE_SQL_SAMPLE('MYSCHEMA')

Una tabla cruzada usando Db2 simple

El código de ejemplo 1 muestra la declaración de selección y el conjunto de resultados para seleccionar un resumen de ventas por vendedor.

select sales_person, sum(sales) as sales
   from sales 
   group by sales_person
   order by sales_person;

SALES_PERSON   SALES
GOUNOT             50	
LEE                91	
LUCCHESSI          14
Código de muestra 1

Cruzando estos datos daría como resultado la línea única que se muestra en el código de muestra 2.

GOUNOT         LEE   LUCCHESSI
     50          91          14
Código de muestra 2

Desafortunadamente, la obtener este resultado de una sola línea requiere bastante trabajo, como se muestra en el código de muestra 3.

select 
   sum(case when sales_person = 'GOUNOT' then sales end) 
    as GOUNOT,
   sum(case when sales_person = 'LEE' then sales end) 
    as LEE,
   sum(case when sales_person = 'LUCCHESSI' then sales end)
    as LUCCHESSI
   from sales;
Código de muestra 3

Examinaremos esta consulta select con más detalle en un momento, pero veamos primero un ejemplo un poco más complicado (y más significativo).

Una tabla cruzada usando Db2 más complicado

En el código de muestra 4 podesmos observar una extensión de la declaración de selección y el conjunto de resultados utilizados en el código de muestra 1. Hemos agregado subtotales por región.

select region, sales_person, sum(sales) as sales
   from sales 
   group by region, sales_person
   order by region, sales_person;

REGION         SALES_PERSON   SALES
Manitoba       GOUNOT             15
Manitoba       LEE                23
Manitoba       LUCCHESSI           3
Ontario-North  GOUNOT              1
Ontario-North  LEE                 8
Ontario-South  GOUNOT             10
Ontario-South  LEE                34
Ontario-South  LUCCHESSI           8
Quebec         GOUNOT             24
Quebec         LEE                26
Quebec         LUCCHESSI          34
Código de muestra 4

Cruzar estos datos es realmente beneficioso, como se muestra en el código de muestra 5.

REGION       GOUNOT         LEE   LUCCHESSI
Manitoba          15          23           3
Ontario-North      1           8           -
Ontario-South     10          34           8
Quebec            24          26          34 
Código de muestra 5

Y la declaración de este select que se requiere no es tan diferente del original, como se muestra en el código de muestra 6.

select 
   region,
   sum(case when sales_person = 'GOUNOT' then sales end) 
    as GOUNOT,
   sum(case when sales_person = 'LEE' then sales end) 
    as LEE,
   sum(case when sales_person = 'LUCCHESSI' then sales end)
    as LUCCHESSI
   from sales
   group by region
   order by region;
Código de muestra 6

El verdadero problema es…

Si examina las declaraciones select en los códigos de muestra 3 y 6, verá que se define una columna para cada uno de los valores posibles para la columna SALES_PERSON. Cuando se ejecuta la instrucción, el valor de la columna SALES se acumulará en la columna correspondiente (GOUNOT, LEE o LUCCHESSI) en función del valor de la columna SALES_PERSON.

Pero, ¿qué sucede si entra en juego un cuarto vendedor? Sí, tenemos que cambiar la declaración de selección y agregar una columna para el nuevo vendedor.

Personalmente, prefiero una solución libre de mantenimiento.

Usando un procedimiento almacenado

El procedimiento almacenado DO_PIVOT acepta seis parámetros:

  1. El nombre del esquema (biblioteca).
  2. El nombre de la tabla.
  3. El nombre de la columna a pivotar (Cruzar).
  4. El nombre de la columna que se agregará para el pivote.
  5. La función agregada que se realizará (el valor predeterminado es SUM).
  6. El nombre de la columna para agrupar (el valor predeterminado es nulo, lo que da como resultado un conjunto de resultados de una sola línea).

La siguiente llamada al procedimiento proporciona el conjunto de resultados que se muestra en el código de ejemplo 2.

CALL DO_PIVOT('SQLSTAND',
              'SALES',
              'SALES_PERSON', 
              'SALES', 
              DEFAULT, 
              DEFAULT);

Y la siguiente llamada al procedimiento proporciona el conjunto de resultados que se muestra en el código de muestra 5.

CALL DO_PIVOT('SQLSTAND',
              'SALES',
              'SALES_PERSON', 
              'SALES', 
              DEFAULT, 
              'REGION');

El procedimiento almacenado DO_PIVOT

El procedimiento DO_PIVOT construye dinámicamente la instrucción select requerida para generar el conjunto de resultados requerido.

Primero, el procedimiento genera una lista de los distintos valores para la columna dinámica solicitada. Luego, construye la instrucción de selección dinámica, agregando una definición de columna para cada uno de los valores distintos en la lista generada.

Finalmente, el procedimiento ejecuta la declaración generada y devuelve el conjunto de resultados.

El código de muestra 7 muestra el código requerido para crear el procedimiento DO_PIVOT. Simplemente copie y pegue, cambie el nombre del esquema y ejecute las declaraciones.

SET SCHEMA = WHER_YOU_WANT_IT;

CREATE PROCEDURE DO_PIVOT
   (IN FOR_SCHEMA CHARACTER (10) , 
    IN FOR_TABLE CHARACTER (10) , 
    IN PIVOT_COLUMN VARCHAR (250) , 
    IN VALUE_COLUMN VARCHAR (250) , 
    IN AGG_FUNCTION VARCHAR (5) DEFAULT 'SUM' , 
    IN GROUP_COLUMN VARCHAR (250) DEFAULT NULL ) 
    LANGUAGE SQL 
    MODIFIES SQL DATA 
    PROGRAM TYPE SUB 
    CONCURRENT ACCESS RESOLUTION DEFAULT 
    DYNAMIC RESULT SETS 1 
    OLD SAVEPOINT LEVEL COMMIT ON RETURN NO 

BEGIN

   DECLARE SQLCODE INTEGER DEFAULT 0 ;
   DECLARE SQL_STATEMENT VARCHAR ( 5000 ) ;
   DECLARE PIVOT_VALUE VARCHAR ( 20 ) ;
   DECLARE PAD CHAR ( 2 ) DEFAULT ' ' ;

   DECLARE C1 CURSOR FOR D1 ;
   DECLARE C2 CURSOR WITH RETURN FOR D2 ;

   SET SCHEMA = FOR_SCHEMA ;

   -- Get the list of values available for the pivot column
   -- Each value will be a column in the return set
   SET SQL_STATEMENT = 'select distinct ' 
                       || PIVOT_COLUMN  
                       || ' from ' 
                       || FOR_TABLE 
                       || ' order by 1' ;

   PREPARE D1 FROM SQL_STATEMENT ;
   OPEN C1 ;

   -- Construct a dynamic select statement for the pivot
   SET SQL_STATEMENT = 'select ' ;

   -- If requested, add the Group By Column 
   -- to the select clause
   IF GROUP_COLUMN IS NOT NULL THEN
      SET SQL_STATEMENT = SQL_STATEMENT || GROUP_COLUMN ;
      SET PAD = ', ' ;
   END IF ;

   -- For each possible value for the Pivot Column, 
   -- add a case statement to perform the requested 
   -- aggregate function on the Value Column
   FETCH NEXT FROM C1 INTO PIVOT_VALUE ;
   WHILE ( SQLCODE >= 0 AND SQLCODE <> 100 ) DO
      SET SQL_STATEMENT = SQL_STATEMENT 
                          || PAD 
                          || AGG_FUNCTION 
                          || '(CASE WHEN ' 
                          || PIVOT_COLUMN 
                          || ' = ''' 
                          || PIVOT_VALUE 
                          || ''' THEN ' 
                          || VALUE_COLUMN 
                          || '  END) AS ' 
                          || PIVOT_VALUE ;
      SET PAD = ', ' ;
      FETCH NEXT FROM C1 INTO PIVOT_VALUE ;
   END WHILE ;
   CLOSE C1 ;

   -- Specify the table to select from
   SET SQL_STATEMENT = SQL_STATEMENT 
                       || ' from ' 
                       || FOR_TABLE ;

   -- If requested, add the Group By Column
   -- to the select clause
   IF GROUP_COLUMN IS NOT NULL THEN
      SET SQL_STATEMENT = SQL_STATEMENT 
                          || ' group by ' 
                          || GROUP_COLUMN 
                          || ' order by ' 
                          || GROUP_COLUMN;
   END IF ;

   PREPARE D2 FROM SQL_STATEMENT ;
   OPEN C2 ;

END ;

LABEL ON ROUTINE DO_PIVOT 
   ( CHAR(), CHAR(), VARCHAR(), VARCHAR(), VARCHAR(), VARCHAR() )  
   IS 'Perform a General Purpose Pivot';

COMMENT ON PARAMETER ROUTINE DO_PIVOT 
   ( CHAR(), CHAR(), VARCHAR(), VARCHAR(), VARCHAR(), VARCHAR() ) 
   (FOR_SCHEMA IS 'Schema for Table' , 
    FOR_TABLE IS 'For Table' , 
    PIVOT_COLUMN IS 'Name of Column to be Pivoted' , 
    VALUE_COLUMN IS 'Column to be Aggregated for Pivot' , 
    AGG_FUNCTION IS 'Use Aggregate Function' , 
    GROUP_COLUMN IS 'Group on Column' ) ;
Código de muestra 7

Este es un procedimiento que he encontrado útil en más de una ocasión y espero que lo encuentres igual de útil. ¡Pero realmente desearía que hubiera una función pivote en DB2!

Crédito a:

Paul Tuohy es CEO de ComCon, una Compañia consultora de iSeries, y un co-fundador de System i Developer. 

Quizás te interese:

Excel en Java
Programación con Swift iOS Parte 1
Programación con Swift iOS Parte 2
Programación con Swift iOS Parte 3 

Te invito a suscribirte a nuestro canal de youtube, donde estaremos subiendo vídeos tutoriales que complementen nuestros artículos.

Capacidades y Habilidades necesarias para ser un desarrollador FULL STACK
Programacion Informática Tecnología

Capacidades y Habilidades necesarias para ser un desarrollador FULL STACK

Que habilidades o conocimientos se requieren para ser un desarrollador Full Stack, acá te informo lo tuani cuales son.
Read More
WebSphere Application Server en Windows Parte #1
Informática Desarrollo Programacion

WebSphere Application Server en Windows Parte #1

¿Que es WebSphere Application Server(WAS)? WebSphere Application Server conocido popularmente como WAS es un servidor de aplicaciones de la familia...
Read More
Procedimiento que regresa una tabla cruzada usando Db2
Informática Programacion

Procedimiento que regresa una tabla cruzada usando Db2

Concepto básico de tabla cruzada usando Db2 El concepto básico de una tabla cruzada es que los valores de fila...
Read More
Primeros pasos con Slim 4 en PHP
PHP Programacion

Primeros pasos con Slim 4 en PHP

Primeros pasos para utilizar Slim 4 en Php, Instalando Apache XAMPP, Composer y creando prin=mera App con Slim.
Read More
Slim para PHP – Micro Framework programación del 2020
PHP Programacion

Slim para PHP – Micro Framework programación del 2020

Hola que tal, como están? espero que muy bien. El día de hoy le vengo a hablar un poco de...
Read More
Conociendo  RPG IV  – AS400
Programacion

Conociendo RPG IV – AS400

Conociendo lenguaje de programación - RPG IV
Read More
Excel en JAVA: Creación y lectura de archivos con Apache POI 4.4
Programacion Java

Excel en JAVA: Creación y lectura de archivos con Apache POI 4.4

Hola que tal, como están?, bienvenido a este pequeño y sencillo articulo acerca de como crear un archivo Excel en...
Read More
Swift-IOS para principiantes, UITableView Parte #3
Programacion Centroamérica Swift

Swift-IOS para principiantes, UITableView Parte #3

Conceptos fundamentales de UITableView Las tablas son comúnmente utilizadas por aplicaciones cuyos datos están altamente estructurados u organizados jerárquicamente. Las...
Read More
Introducción a Swift IOS Parte #2
Informática Swift

Introducción a Swift IOS Parte #2

En este Post aprenderemos a crear nuestra primera app básica usando Swift IOS, la cual estará conformado por dos campos de textos donde se digitarán números que a través de botones se ejecutarán acciones básicas de una calculadora como sumar, restar, multiplicar y dividir.
Read More
Introducción a Swift-IOS Parte#1
Informática Programacion Swift

Introducción a Swift-IOS Parte#1

Swift es un lenguaje de programación creado por Apple, el cual esta enfocado para desarrollar aplicaciones que corran en iOS, Mac OS (OSX), TvOS y WatchOS presentado oficialmente en el año 2014 y diseñado para que se pudiera integrar con Cocoa y Cocoa Touch
Read More
{"posts_per_page":"10","cat":["151"],"design":"design-1","pagination":"false","gridcol":"2","showDate":"true","showCategory":"true","showContent":"true","words_limit":"20","showreadmore":"true","order":"DESC","orderby":"post_date","showAuthor":"true","media_size":"large","show_tags":"true","show_comments":"true"}
Luis Fonseca

Amplia experiencia en Desarrollo de Software usando lenguajes de programación: Java, C#, Swift - iOS, Angular 8, Node JS con Express.js, en base de datos MS Sql Server, Db2 For i y Postgresql.Apasionado por la Tecnología, Animes y Mangas principalmente de Dragón ball y Saint Seiya.

    Hasta ahora, 1 comentario

    WebSphere Application Server en Windows Parte #1 – Esta Tuani Publicado el8:00 pm - enero 6, 2020

    […] Tablas Cruzadas en DB2 For i […]

    Deja un comentario

    shares
    A %d blogueros les gusta esto: