Filas a Columnas

[Gallosuarez]

amerika111:

Lo sorprendente sería que dijeras que ese código te hubiera funcionado. Mira, no creo que estés entendiendo el concepto de CTE's (veo que, además de estar pésimamente implementado, el concepto no se está entendiendo).

Por otro lado, en este mismo foro ya se había tratado este tema con anterioridad. El compañero fjcg02 mencionó entonces en aquella ocasión que utilizar CTE's no resolvía el problema si existían varios conceptos. Lo cual no es un problema, puesto que se resuelve utilizando bloques ejecutables (Execute Block). Que es mas o menos lo que él hace en dicho procedimiento almacenado, con la diferencia que el código resultante es sumamente robusto y eficiente (amen de ser una solución elegante). Otra cosa, el código completo se puede armar desde la misma aplicación (ventaja de hacerlo con Execute block).

Bueno, hecha esta aclaración le digo a amerika111 que primero tiene que entender bien el concepto. Resolver su problema no debe de llevarse mucho tiempo.

Saludos,
Gerardo Suárez Trejo
PD. Estoy creando tutoriales que tratan todas estas cuestiones ... espero pronto subirlos para haber si les puede ser útiles a alguien... saludos nuevamente ...

[[fjcg02]]

bbbbbbbbbbbbbbbbbbbbbbbbbb

[[fjcg02]]

Empezado por fjcg02

bbbbbbbbbbbbbbbbbbbbbbbbbb

Llevo un rato intentando entender el WITH este del demonio, y no acabo de cogerle el punto ...

[guillotmarc]

En mi opinión os estáis complicando mucho. Solucionarlo con subconsultas normalmente es bastante sencillo (claro que solo se puede hacer cuando el nº de columnas está prefijado de antemano).

Sería algo del tipo :

Código SQL [-]
select mes
         (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'A') as ventas_A,
         (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'B') as ventas_B,
         (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'C') as ventas_C
from meses

Saludos.

[amerika111]

no pues sigo en las mismas, ya hasta regañada sali,


trate de hacerlo con lo de CTE's pero nada no se como utilizar lo que me han comentado del case o la subconsulta

Código SQL [-]
with tabla as
(select PAGOS_NOMINA_DET.PAGO_NOMINA_ID, PAGOS_NOMINA_DET.CONCEPTO_NO_ID,CONCEPTOS_NO.NOMBRE,PAGOS_NOMINA_DET.IMPORTE,EMPLEADO_ID FROM PAGOS_NOMINA_DET  INNER JOIN  CONCEPTOS_NO
on CONCEPTOS_NO.CONCEPTO_NO_ID = PAGOS_NOMINA_DET.CONCEPTO_NO_ID  )
SELECT (NOMBRE from tabla WHERE ) FROM tabla
select IMPORTE
       (SELECT IMPORTE from tabla where CONCEPTO_NO_ID =104) as 'SALARIO MIN'
FROM tabla

[amerika111]

Código SQL [-]
with tabla as
(select PAGOS_NOMINA_DET.PAGO_NOMINA_ID, PAGOS_NOMINA_DET.CONCEPTO_NO_ID,CONCEPTOS_NO.NOMBRE,PAGOS_NOMINA_DET.IMPORTE,EMPLEADO_ID FROM PAGOS_NOMINA_DET  INNER JOIN  CONCEPTOS_NO
on CONCEPTOS_NO.CONCEPTO_NO_ID = PAGOS_NOMINA_DET.CONCEPTO_NO_ID  )

select IMPORTE
       (SELECT IMPORTE from tabla where CONCEPTO_NO_ID =104) as 'SALARIO MIN'
FROM tabla

[Gallosuarez]

fjcg02:

Muy bien ... ahora ya nos estamos entendiendo ... bueno pues manos a lo obra:

Lo primero que hay que notar es que solo tienes una solo tabla, por lo que de ahí vamos a obtener el pivote ... y del pivote vamos a obtener nuestro resultado final. Por otro lado, es mejor empezar con un caso pequeño (donde los conceptos son fijos). Posteriormente nos movemos hacia un caso más difícil, ¿vale?.

Corre esta sentencia primero (este sería nuestro pivote). Sean benévolos conmigo a lo mejor tengo errores en el nombre de un tabla/campo, etc, etc. Necesito que me digas si funciona correctamente dicha sentencia.

Código SQL [-]
SELECT 
  POBLACION,
  ESTADOCIVIL,
  COUNT(*) AS CANTIDAD
FROM CLIENTE
GROUP BY POBLACION, ESTADOCIVIL

Despues si todo esto se ejecutó sin errores (doy por hecho que así es), creo mi tabla principal donde aparecerán los conceptos.

Código SQL [-]
WITH PIVOTE AS (
  SELECT 
    POBLACION,
    ESTADOCIVIL,
    COUNT(*) AS CANTIDAD
  FROM CLIENTE
  GROUP BY POBLACION, ESTADOCIVIL
)
SELECT
 P.POBLACION,
 COALESCE(P_CASADOS.CANTIDAD, 0) AS CASADOS,
 COALESCE(P_SOLTEROS.CANTIDAD, 0) AS SOLTEROS
FROM PIVOTE P
  LEFT JOIN PIVOTE P_CASADOS
    ON P.POBLACION = P_CASADOS.POBLACION
    AND P_CASADOS.ESTADOCIVIL = 'Casado'
  LEFT JOIN PIVOTE P_SOLTEROS
    ON P.POBLACION = P_SOLTEROS.POBLACION
    AND P_SOLTEROS.ESTADOCIVIL = 'Soltero'
    .
    .

Espero que esto despeje un poco sus dudas. Porqué decimos que esta forma de solucionar el problema es sumamente eficiente, porque la tabla "pivote" solo se ejecuta una sola vez, sin importar cuantos conceptos se agreguen después. Otra cosa que hay que hacer notar es el uso correcto de índices. En este caso no sé si haya indices, sino no los hay, la sentencia pivote tendrá que recorrer toda la tabla (aunque solo sea una solo vez).

Saludos,
Gerardo Suárez Trejo

[[RONPABLO]]

Yo hago algo parecido y me toca que construirlo desde delphi, yo hago algo así como esto:

Código Delphi [-]

   sql := '';
   while not QConceptos.Eof do
   begin
      if sql <> '' then
        sql := sql + ',';
      Concepto := QConceptos.FieldByName('Concepto').AsString;
      sql := sql + ' Case when Campo = ' + QuotedStr(Concepto) + ' THEN campoimporte else 0 end as ' + Concepto
      QConceptos.Next;
   end;
  
   sql := 'Select Id_empleado, ' +  sql + ' from ....'

Donde el query QConceptos es el siguiente:

Código SQL [-]

   Select distinct Concepto from Conceptos

Todo esto lo hago sin comprobar, es solo para dar una idea de como lo hago.

[[RONPABLO]]

Empezado por guillotmarc

En mi opinión os estáis complicando mucho. Solucionarlo con subconsultas normalmente es bastante sencillo (claro que solo se puede hacer cuando el nº de columnas está prefijado de antemano).

Sería algo del tipo :

Código SQL [-]
select mes
          (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'A') as ventas_A,
          (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'B') as ventas_B,
          (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'C') as ventas_C 
from meses

Saludos.

Viendo esta respuesta creo que combinándola con mi respuesta anterior podría también funcionar muy bien.

[mRoman]

Empezado por guillotmarc

En mi opinión os estáis complicando mucho. Solucionarlo con subconsultas normalmente es bastante sencillo (claro que solo se puede hacer cuando el nº de columnas está prefijado de antemano).

Sería algo del tipo :

Código SQL [-]
select mes
         (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'A') as ventas_A,
         (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'B') as ventas_B,
         (select sum(unidades) from ventas where ventas.mes = meses.mes and producto = 'C') as ventas_C
from meses

Saludos.

Hola guillotmarc, en que version de firebird puedes hacer esto?

Uso la version 2.0

[[fjcg02]]

Hola de nuevo,
estoy haciendo unas pruebas, y la verdad, no me están convenciendo. A no ser que esté haciendo algo mal, y que agradecería que me lo indicarais si alguien lo sabe.

Mi intención es sacar una consulta con las poblaciones y los estados civiles de los clientes. La tabla es de clientes, en donde hay id, nombre, población y estado civil. La tabla tiene 16.500 registros. Existen 4 estados civiles ,a saber: CASADO, SOLTERO, SIN DEF. y VIUDO ( tened en cuenta que no está en producción, son pruebas ).

Una primera consulta sería

Código SQL [-]
select poblacion,
sum ( case when estadocivil='CASADO' then 1 ELSE 0 END) AS CASADO,
sum ( case when estadocivil='SOLTERO' then 1 ELSE 0 END) AS SOLTERO,
sum ( case when estadocivil='SIN DEF.' then 1 ELSE 0 END) AS SINDEF,
sum ( case when estadocivil='VIUDO' then 1 ELSE 0 END) AS VIUDO
from cliente
group by poblacion

Respuesta perfecta, tiempo de consulta aprox 3 segundos.

Utilizando WITH

Código SQL [-]
 with
  estadosciviles as (
    select estadocivil as EC, count(idcliente) as NUMERO
    from cliente
    group by estadocivil)
    select poblacion,
    ECC.numero as CASADOS,
    ECS.numero as SOLTEROS,
    ECSD.numero as SINDEF,
    ECV.numero as VIUDOS
    from cliente  C
    left join estadosciviles ECC on C.estadocivil=ECC.EC
    and ECC.EC='CASADO'
    left join estadosciviles ECS on C.estadocivil=ECS.EC
    and ECS.EC='SOLTERO'
    left join estadosciviles ECSD on C.estadocivil=ECSD.EC
    and ECSD.EC='SIN DEF.'
    left join estadosciviles ECV on C.estadocivil=ECV.EC
    and ECV.EC='VIUDO'

Resultado inexacto. Me devuelve más de una fila por población, y datos un poco extraños.
Tiempo de respuesta. Me deja el equipo cuajado un par de minutos al menos.

Me da la sensación de que estoy haciendo mal la consulta. A ver si alguien me puede poner luz...

Gracias

[guillotmarc]

Hola.

Si en tu primero consulta, sencilla, ya obtienes el resultado el resultado que estás buscando, y en un tiempo óptimo, ¿ porqué te quieres complicar la vida sacándolo mediante CTE's ?.

NOTA: Para agilizar un poco más esa primera consulta, puedes añadir un índice por población (de esa forma facilitas la agrupación por ese campo).

Saludos.

[[fjcg02]]

Empezado por guillotmarc

Hola.

Si en tu primero consulta, sencilla, ya obtienes el resultado el resultado que estás buscando, y en un tiempo óptimo, ¿ porqué te quieres complicar la vida sacándolo mediante CTE's ?.

NOTA: Para agilizar un poco más esa primera consulta, puedes añadir un índice por población (de esa forma facilitas la agrupación por ese campo).

Saludos.

Hola,
estoy comparando la manera de obtener los datos, con la primera consulta ( que la creo dinámicamente con una procedure ) o con CTE's.
La segunda opción me está dando un rendimiento muy pobre, de ahí mi consulta de si estoy haciendolo bien o no.

Saludos

[guillotmarc]

Hola.

Empezado por fjcg02

Hola,
estoy comparando la manera de obtener los datos, con la primera consulta ( que la creo dinámicamente con una procedure ) o con CTE's.
La segunda opción me está dando un rendimiento muy pobre, de ahí mi consulta de si estoy haciendolo bien o no.

¿ Porqué necesitas construir dinámicamente la primera consulta ?. ¿ El número de columnas no es siempre fijo ?.

Saludos.

[Gallosuarez]

Empezado por guillotmarc

Hola.
Si en tu primero consulta, sencilla, ya obtienes el resultado el resultado que estás buscando, y en un tiempo óptimo, ¿ porqué te quieres complicar la vida sacándolo mediante CTE's ?.

No estoy muy de acuerdo en que utilizar CTE's te compliques la vida ... hagamos lo siguiente:

fjcg02 puedes poner la estructura de tus tablas (algo así como lo que hizo amerika111, en un archivo gpeg como el que ella subio). Después hacemos el ejercicio y verán que es muy fácil.

El secreto está primero en programar la "tabla" que te servirá de pivote ... posteriormente lo único que tienes que hacer es llamarla las veces que necesites dependiendo de los "conceptos" que quieras que aparezcan en las columnas ... ¿me explico?.

Saludos
Gerardo Suárez Trejo

[guillotmarc]

Hola.

Empezado por Gallosuarez

No estoy muy de acuerdo en que utilizar CTE's te compliques la vida ... hagamos lo siguiente:

Está claro que la vida no te la complican las herramientas (CTE's, subconsultas, vistas, SP's, etc. ...) sino que para eso ya nos bastamos nosotros mismos, con el uso que damos a estas herramientas.

En este caso tan sencillo parece que recurrir a CTE's es como intentar matar mosquitos a cañonazos.

Saludos.