Rotación de tablas [Crosstab-Queries]

Por Hermilson Tinoco

 Descargar ejemplos de este artículo (3 KB).

La información de este artículo aplica para Microsoft SQL Server 2000.

Nota del autor
Muchos de los profesionales que programan con Transact-SQL se preguntan frecuentemente cómo se debe organizar una consulta para mostrar los datos de una columna como columnas independientes. Como esto me sucedió a mi alguna vez y veo esta clase de preguntas constantemente en los grupos de discusión, me dediqué a escribir este artículo que recopila la solución básica a esta pregunta (CrossTab) y una solución mejorada y más dinámica.

Contenido

 Introducción
 Consulta de Ejemplo
 Solución
 Ventajas y limitaciones
 Conclusión

Introducción

A veces se tiene tablas donde nos gustaría ver nuestras filas como columnas, incluso agrupando de alguna forma nuestra información o solamente visualizándola; esto se conoce como Crosstab Query. Aunque esta  utilidad se puede encontrar en otras herramientas tales como Microsoft Access, en SQL Server 2000 sólo se pueden utilizar comandos Transact SQL para tratar esta información; por este motivo he decidido publicar un procedimiento que permite visualizar esta clase de consultas. El resultado final lo obtuve con ayuda del artículo publicado por Itzik Ben-Gan. Esta utilidad esta planeada para la versión SQL Server Yukon(2005).

Consulta de Ejemplo

Para mostrar el funcionamiento y resultado del procedimiento, se propone un problema a solucionar con el siguiente esquema: se tiene una base de datos con la tabla tblPedidos en la cual se almacenan los datos principales de un pedido: Número, Fecha de Expedición, Fecha de Entrega, Vendedor, Valor Total y Estado (Ver Figura 1). En este caso se desea obtener una consulta que muestre la cantidad en dinero de las ventas mensuales de las facturas aprobadas por cada vendedor.

Figura 1. Volver al texto.

Solución

Puede decirse que existen dos métodos para generar una solución a esta consulta: el método estático, y el dinámico.

Método estático

Este método utiliza la instrucción CASE como su principal herramienta, aplicando un CASE a cada valor de pivote y agrupando su resultado por medio de una función de agregado como SUM. El valor a agregar puede ser de dos tipos: un valor fijo, u otra columna dentro de la tabla. El resultado de aplicar este método al problema anterior es el siguiente:

Script generado por el método estático:

--Cantidad de ventas de cada mes x vendedor
SELECT f_vendedor,
            SUM(CASE MONTH(f_fecha_expedicion)
                WHEN 4 THEN f_vlor_total
                ELSE 0
              END) AS Abril,
            SUM(CASE MONTH(f_fecha_expedicion)
                WHEN 5 THEN f_vlor_total
                ELSE 0
              END) AS Mayo,
            SUM(CASE MONTH(f_fecha_expedicion)
                WHEN 6 THEN f_vlor_total
                ELSE 0
              END) AS Junio
FROM  #tblPedidos
GROUP BY f_vendedor
ORDER BY f_vendedor

Resultado:

El problema potencial que tiene este método es que se debe conocer previamente los valores del campo pivote, además de que cuando se ingresen nuevos pedidos en otros meses, la consulta será obsoleta; en el ejemplo, el mes 7 no se puede visualizar.

Esta consulta debe ser modificada cuando un valor pivote cambie o se adicione uno nuevo, implicando adicionar un nuevo CASE.

Método dinámico

Lo mejor sería tener un método que permita ejecutar una consulta de manera dinámica, que no necesite conocer los valores pivote ni estar actualizando el código de la consulta. Ese es el objetivo de este artículo y se explica a continuación.

La idea principal es crear un procedimiento que reciba varios parámetros de entrada con el fin de devolver una consulta de la tabla pivote, generando las columnas de destino de manera dinámica; este stored procedure se puede crear en una base de datos exclusiva o se puede crear en la base de datos máster para permitir que el procedimiento sea invocado desde cualquier base de datos.

El procedimiento consta de siete partes las cuales realizan las siguientes funciones:

1. Declaración de parámetros de entrada: estos parámetros son los que hacen a la consulta dinámica y se utilizan de la siguiente forma:

   1. strSQLOut: Parámetro de salida, contiene la cadena con la consulta final. Si la consulta tiene una longitud mayor a 8.000 carácteres solo retorna el primer fragmento de la misma.

   2. table: Esta es la tabla que se utilizará como fuente de datos en la consulta.

   3. onrows: parámetro utilizado para determinar el nombre de la columna en la tabla fuente que será utilizada para obtener los valores pivote; en otras palabras, son las filas principales de la consulta final. Esta columna es la que se aplica a las sentencias Group by y Order by.

   4. onrowsalias: si se desea colocar un alias (o título) a la columna principal se debe diligenciar este parámetro, de lo contrario éste es tratado como Null.

   5. oncols: parámetro con el nombre de la columna en la tabla fuente que será utilizada para obtener los valores de las columnas en la consulta final.

   6. sumcol: nombre de la columna en la tabla fuente que se desea sumar. Si solo se desea contar la cantidad de registros este parámetro debería tener un valor de Null.

   7. onWhereKeys: sentencia Where aplicada a los valores pivote o columna detallada en el parámetro onrows. Este parámetro no es obligatorio. Ej.: ‘estado > 0’.

   8. onWhere: sentencia Where aplicada a la consulta construida dinámicamente y que realiza un filtro global de los datos fuente. Este parámetro no es obligatorio.

   9. onCaseWhere: sentencia Where aplicada a los segmentos CASE ubicados en el cuerpo de la consulta. Este parámetro no es obligatorio. Ej.: 'and cantidad = 10'.

   10. onInsertTable: nombre de la tabla donde se desea insertar los datos resultantes. Este parámetro no es obligatorio. Ej.: 'tblVentas' --'##table1'.

2. Inicializar la consulta: se inicializa la cadena que contendrá el Select final con la respectiva instrucción y el nombre de la columna principal, acompañada del alias si es necesario.

3. Leer los valores pivote: aquí se leen los valores que van a estar relacionados como columnas dentro de la consulta, estos datos se pueden prefiltrar con el parámetro onWhereKeys.

4. Construir la instrucción: En esta parte se construye la parte de la instrucción Select que especifica las columnas a ser retornadas por la consulta. Para construir esta parte se lee los valores pivote uno a uno, formando con estos los respectivos fragmentos CASE de la consulta, similares a los aplicados en el método estático. En esta parte se utiliza la variable onCaseWhere para permitir un filtro extra de los datos por agrupar.

5. Finalizar la consulta: se finaliza la instrucción Select adicionando a ésta las sentencias de selección de la fuente de datos, el agrupamiento, ordenamiento y si se requiere el filtro de datos utilizando la instrucción Where y el parámetro onWhere.

6. Ejecución y retorno: este paso es el encargado de ejecutar la instrucción construida utilizando la sentencia Execute de SQL Server; además se encarga de asignar esta instrucción al parámetro de salida.

7. Inserción de datos: si el usuario lo desea puede realizar una inserción de estos datos dentro de una tabla fija o una temporal. Si la tabla es fija, ésta debe de existir y cumplir con las condiciones normales de compatibilidad de estructura; si la tabla es temporal ésta se crea dinámicamente al momento de ejecutar este procedimiento, por utilidad esta tabla debería de ser una tabla global.

Algunos ejemplos del uso de este procedimiento son:

• Cantidad de ventas de cada mes por vendedor:

  EXEC sp_rotate_table
              @ontable = '#tblPedidos',
              @onrows = 'f_vendedor',
              @oncols = 'mONTH(f_fecha_expedicion)',
              @sumcol = 'f_vlor_total'

• Cantidad de ventas de cada mes x vendedor, de las facturas aprobadas, colocando un alias a la fila principal, e insertando el resultado en una tabla global.

  Declare @strSQLOut varchar(8000)
  EXEC sp_rotate_table
              @strSQLOut output,
              @ontable = '#tblPedidos',
              @onrows = 'f_vendedor',
              @onrowsalias = 'Vendedor',
              @oncols = 'mONTH(f_fecha_expedicion)',
              @sumcol = 'f_vlor_total',
              @onCaseWhere = 'and f_estado > 0',
              @onInsertTable = '##table_rotate'
   
  SELECT @strSQLOut
  SELECT * FROM ##table_rotate
  DROP TABLE ##table_rotate

• Resultado:

  Vendedor 4 5 6 7 1 125000.00 992450.00 211000.00 78200.00 2 20000.00 .00 458000.56 65500.00 3 .00 .00 100000.00 .00

• Cantidad de ventas de cada mes x vendedor, de las facturas aprobadas, colocando un alias a la fila principal, e insertando el resultado en una tabla global.

  Declare @strSQLOut varchar(8000)
  EXEC sp_rotate_table
              @strSQLOut output,
              @ontable = '#tblPedidos',
              @onrows = 'f_vendedor',
              @onrowsalias = 'Vendedor',
              @oncols = 'mONTH(f_fecha_expedicion)',
              @sumcol = 'f_vlor_total',
              @onCaseWhere = 'and f_estado > 0',
              @onInsertTable = '##table_rotate'
   
  SELECT @strSQLOut
  SELECT * FROM ##table_rotate
  DROP TABLE ##table_rotate

• Resultado:

  Vendedor 4 5 6 7 1 125000.00 992450.00 211000.00 0.00 2 20000.00 .00 458000.56 65500.00 3 0.00 0.00 0.00 0.00

Ventajas y limitaciones

El procedimiento trabaja de manera dinámica en conjunto con los parámetros insertados por el usuario.

Permite utilizar valores numéricos, de tipo varchar, char y de tipo fecha (no olvidar que se incluye los valores de hora).

Permite generar una consulta crosstab mostrando e insertando el resultado en una tabla auxiliar dependiendo de la necesidad del usuario.

Pero el procedimiento tiene limitaciones tales como: La instrucción SQL generada por el procedimiento no puede contener mas de 16.000 carácteres, esto va relacionado a la cantidad de valores pivote y es más un limitante del tipo de dato varchar. Para Script resultante [strSQLOut] el tamaño máximo es de 8.000 carácteres.

• Requiere la administración de permisos sobre el procedimiento creado en la base de datos máster; para evitar este inconveniente se puede convertir este stored procedure a un procedimiento del sistema (esta tarea no se amplía porque este no es tema del artículo).

• Como valores pivote no se pueden utilizar NULLs; en estos casos se debe utilizar la función IsNull() .

• El procedimiento no funciona adecuadamente con valores de tipo text o ntext.

• Puede tener los problemas habituales del SQL Dinámico; aunque éstos se pueden subsanar con un buen manejo de la seguridad.

Conclusión

Esta es una solución lo suficientemente dinámica y flexible como para permitir obtener los resultados deseados con la menor cantidad de esfuerzo. Dependiendo de la complejidad de la consulta, el procedimiento brinda diversas herramientas tales como parámetros, y así se logra obtener el resultado esperado.

Al procedimiento se le pueden agregar otras funcionalidades, tal como incluir como columna principal un campo que no pertenezca a la tabla pivote; algo así como una descripción o explicación de lo que contiene el registro, por ejemplo el nombre del vendedor. Esta y otras funcionalidades no se incluyen en este artículo con el fin de brindar un procedimiento base dispuesto a ser moldeado a las necesidades particulares.

Hermilson Tinoco trabaja como Analista de Sistemas desde hace más de 7 años, con ambientes Microsoft (VB6, .NET y SQL Server) y como Consultor en aplicaciones corporativas e implementaciones de bases de datos empresariales (SQL Server y Oracle). Es Ingeniero de Sistemas de la Universidad Santiago de Cali, Colombia, e instructor en esa misma universidad y en varios CTECs de esa ciudad. Cuenta además con la certificación MCP desde 2001, habiendo obtenido luego las certificaciones MCSD (VB6), MCAD, MCSD .net, MCT y MCDBA.