Aprender a transformar las filas en columnas de una tabla, te permitirá mostrar los datos de una forma diferente a lo habitual en tus PL-SQL Procedures en Oracle, donde la información se podrá apreciar de distintos ángulos o distintas perspectivas.
Cuando trabajas construyendo PL-SQL Procedures en Oracle, muchas veces se requiere desplegar la información de distinta forma, como por ejemplo, transformar las filas de una tabla en columnas, lo que no resulta ser una tarea fácil de realizar.
A muchos desarrolladores les cuesta mucho construir este tipo de tareas y de hecho puede llegar a ser una tarea muy difícil que muchos no logran finalizar, y si lo logran han debido construir un código gigantesco.
A mí siempre me ha gustado buscar la simplicidad para hacer las cosas; y logre encontrar una forma más simple de convertir las filas a columnas de una tabla, la que cuando la descubrí, me sorprendió mucho por lo fácil que es de implementar en los PL-SQL Procedures en Oracle. Revisemos de inmediato esta técnica con un ejemplo práctico de como se debe hacer.
Ejemplo #1: Supone que tienes una tabla que contiene los posibles estados civiles de una persona, los que debes mostrar como columnas, veamos como hacerlo:
Tabla t_estado_civil: Esta tabla contiene 5 estados civiles definidos para mostrarlos como columna.
/* PL-SQL Procedures en Oracle */
id_estado_civil descripcion
1 SOLTERO
2 CASADO
3 SEPARADO
4 VIUDO
5 OTROS
Query para dejar las filas como columnas:
/* PL-SQL Procedures en Oracle */
Select
‘Estados:’ titulo,
max( decode( eciv.id_estado_civil, 1, eciv.descripcion)) col_1,
max( decode( eciv.id_estado_civil, 2, eciv.descripcion)) col_2,
max( decode( eciv.id_estado_civil, 3, eciv.descripcion)) col_3,
max( decode( eciv.id_estado_civil, 4, eciv.descripcion)) col_4,
max( decode( eciv.id_estado_civil, 5, eciv.descripcion)) col_5
From t_estado_civil eciv;
Esta simple query permite mostrar las filas de la tabla t_estado_civil como columna, mostrándolas de la siguiente forma:
/* PL-SQL Procedures en Oracle */
Titulo col_1 col_2 col_3 col_4 col_5
Estados: SOLTERO CASADO SEPARADO VIUDO OTROS
El truco se encuentra en utilizar la función de grupo MAX, la que permite obtener el valor máximo o mayor de una columna.
Ejemplo #2: En el ejemplo anterior se logró mostrar las filas como columnas de la tabla t_estado_civil, veamos ahora como mostrar datos congruentes con estas columnas, usando la misma técnica:
Query para agregar datos como filas a cada columna:
/* PL-SQL Procedures en Oracle */
Select
‘Estados:’ departamento,
max( decode( eciv.id_estado_civil, 1, eciv.descripcion)) col_1,
max( decode( eciv.id_estado_civil, 2, eciv.descripcion)) col_2,
max( decode( eciv.id_estado_civil, 3, eciv.descripcion)) col_3,
max( decode( eciv.id_estado_civil, 4, eciv.descripcion)) col_4,
max( decode( eciv.id_estado_civil, 5, eciv.descripcion)) col_5
From t_estado_civil eciv
Union all /* PL-SQL Procedures en Oracle */
Select
to_char(emp.id_departamento) departamento,
to_char(sum( decode( emp.id_estado_civil, 1, 1, 0))) cant_col_1,
to_char(sum( decode( emp.id_estado_civil, 2, 1, 0))) cant_col_2,
to_char(sum( decode( emp.id_estado_civil, 3, 1, 0))) cant_col_3,
to_char(sum( decode( emp.id_estado_civil, 4, 1, 0))) cant_col_4,
to_char(sum( decode( emp.id_estado_civil, 5, 1, 0))) cant_col_5
From t_empleado emp
Group by emp.id_departamento
A la query del primer ejemplo, se agrego una segunda query unida por el union all, para obtener la cantidad de estados civiles acumulados por cada departamento. Esta consulta entrega los siguientes resultados:
/* PL-SQL Procedures en Oracle */
Departamento col_1 col_2 col_3 col_4 col_5
Estados: SOLTERO CASADO SEPARADO VIUDO OTROS
101 10 15 0 0 2
102 0 0 4 12 0
105 0 32 8 0 13
El truco en esta segunda query se encuentra en la función de grupo SUM la que permite unificar los datos en una sola fila.
Te propongo que pruebes de inmediato estos ejemplos en tu Base de Datos con tus propios PL-SQL Procedures en Oracle y PL-SQL Function en Oracle, para que comiences a ejercitar la mano y aprendas a dominar esta nueva técnica en tus PL-SQL Packages en Oracle.
Estoy muy interesado en conocer tu opinión y/o comentario sobre este artículo, también cuéntame sobre qué es lo que quieres saber de PL-SQL Procedures en Oracle y/o dime cuál es tu principal frustración a la hora de trabajar con PL-SQL Procedures en Oracle. Estaré muy atento respondiendo tus preguntas y/o comentarios, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Director del Curso “Domina Tus Packages En Oracle“
| ndux98duehduxge67rtf |
Hola que tal Roberto, muy bueno el tutorial…
Tendrás mas ejemplos sobre esto??
Tengo el siguiente problema:
tengo un query con:
ID Type
8 A
8 L
8 Z
10 P
4 U
4 N
22 R
22 R
22 R
necesito presentar:
ID Types
8 A,L,Z
10 P
4 U
22 R
En el caso de 22 se repite el type, solo debo mostrarlo una vez, uso Oracle 10g.
Espero tengas algo que me pueda ayduar.
saludos.
Hola Ulises, te agradezco mucho el comentario, te adjunto el query que me solicitaste, si tienes mas letras en el campo type solo tienes que agregar otro decode para cada una de ellas, espero haberte ayudado.
select
dat.id,
trim(max(decode(dat.type,’A’,’A’,’ ‘)) ||’ ‘||max(decode(dat.type,’L’,’L’,’ ‘))
||’ ‘||max(decode(dat.type,’Z’,’Z’,’ ‘))
||’ ‘||max(decode(dat.type,’P’,’P’,’ ‘))
||’ ‘||max(decode(dat.type,’U’,’U’,’ ‘))
||’ ‘||max(decode(dat.type,’N’,’N’,’ ‘))
||’ ‘||max(decode(dat.type,’R’,’R’,’ ‘))
) type
from tu_tabla dat
group by dat.id;
Saludos
Roberto Vicencio.
PD: Reclama tu reporte gratuito de “Cómo Construir un Select Eficiente en Oracle en Solo 7 Pasos”, suscríbete para que sigas recibiendo información gratuita pero de mucho valor.
Hola Roberto;
No me funciona el segundo query :
Select
to_char(emp.id_departamento) departamento,
sum( decode( emp.id_estado_civil, 1, count(1), 0)) cant_col_1,
sum( decode( emp.id_estado_civil, 2, count(1), 0)) cant_col_2,
sum( decode( emp.id_estado_civil, 3, count(1), 0)) cant_col_3,
sum( decode( emp.id_estado_civil, 4, count(1), 0)) cant_col_4,
sum( decode( emp.id_estado_civil, 5, count(1), 0)) cant_col_5
From t_empleado emp
Group by emp.id_departamento ;
Me meustra el siguiente mensaje :
ORA-00937: not a single-group group function
Aparentemente es por el uso del COUNT(1) ademàs del uso del SUM.
A que prodrìa deberse…? El Oracle que uso es el 9i.
Desde ya gracias.
Saludos;
Enrique.
Hola Enrique, gracias por comunicarte, ese error ocurre porque te falta agregar el campo id_estado_civil en el group by, fíjate que el ejemplo del artículo si lo tiene, debes agregarlo de esta forma:
Group by emp.id_departamento, emp.id_estado_civil;
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Hola,
Respecto al problema de Ulises, esto se consigue exactamente con la función wm_concat(), que devuelve una lista de todos los elementos de cada grupo.
Es una función “no documentada” de Oracle, pero funciona perfectamente.
Se puede utilizar to_string(wm_concat(Type)) si es necesario.
A partir de la versión 11 Oracle aconseja utilizar la función listagg(), que es más “oficial” que la otra.
Amplío mi comentario anterior:
Para que no se repitan los elementos se debe usar “distinct”:
to_string(wm_concat(distinct Type))
Hola muchas gracias por compartir tus conocimientos …
estube probando el truco que nos compartiste, pero me sale un error de datatypes, en el primer query el campo del select que devuelve es tipo texto y al hacer el union all con el segundo query con el campo count(1) devuelve tipo numerico, creo q por ahi es q no me funciona, te agradeceria muchisimo si despejaras mis dudas, de antemano gracias …
Hola Javier, gracias por comunicarte, cuando haces un union all las querys que unes deben tener la misma cantidad de campos, el tipo de dato de cada columna de la primera query debe coincidir con los tipos de dato de la segunda query, es decir, si la primera query tiene un campo varchar2 y otro number, la segunda query tambien debe tener 2 campos, el primero varchar2 y el segundo number, gracias.
PD: Ya obtuviste mi reporte gratuito sobre “Como Construir un SELECT Eficiente en Oracle en solo 7 Pasos”, si aún no lo has hecho puedes conseguirlo haciendo clic aquí:
https://dominatuspackagesen0racle.com
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Muchas gracias, esta info me ayudó mucho!!
Hola Mayra, gracias por comunicarte y por tu comentario, me da mucho gusto que la información te sea útil y te invito a seguir visitando mi blog, gracias.
PD: Ya obtuviste el libro-reporte sobre ‘Cómo Construir un SELECT Eficiente en Oracle en Solo 7 Pasos’, si aún no lo has hecho solicítalo Gratis desde aquí:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Con la tabla de estados civiles fijos es sencillo. Pero como hacer cuando esto no es fijo. No se sabe cuantos decode se necesitan. En sqlserver se permite crear tablas con columnas dinamicas entonces se puede invertir la informacion de filas en columnas. Como sería en Oracle?
Hola Jorge, gracias por comunicarte, muy buena tu pregunta esta muy interesante, para crear columnas cuando las filas no son fijas en Oracle debes usar lo que se denomina Sql Dinámico, de esta forma puedes tomar todas las filas de una tabla, convertirlas en columnas y crear una nueva tabla, debes tener la precaución de que la tabla de origen no tenga miles de filas ya que se volvería algo inmanejable, finalmente podrás manipular la tabla que creaste que es mucho mas fácil, para esto debes hacer algo como lo siguiente:
lst_sql varchar2(500);
begin
lst_sql:= ‘ ‘;
— Aqui se crea el sql dinámico convirtiendo todas las filas en columnas de la tabla t_estado_civil.
for lin_reg in (select id_estado_civil from t_estado_civil)
loop
lst_sql:= lst_sql || ‘, max( decode( eciv.id_estado_civil, ‘||to_char(lin_reg.id_estado_civil)
||’, eciv.descripcion)) col_’||to_char(lin_reg.id_estado_civil);
end loop;
lst_sql:= lst_sql || ‘ From t_estado_civil eciv’;
— Aqui se ejecuta el sql dinámico para crear la tabla tmp_filas_columnas con las filas convertidas en columnas.
EXECUTE IMMEDIATE (‘create table tmp_filas_columnas as select ”Anexos:” titulo’||lst_sql);
end;
PD: Ya obtuviste mi libro-reporte gratuito sobre “Cómo Construir un SELECT Eficiente en Oracle en Solo 7 Pasos”, si aún no lo tienes puedes obtenerlo desde el siguiente enlace:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
gracias el reporte esta fabuloso, una pregunta si tengo algo como:
codigoEstudiante Materia
1 Calculo
1 Matematicas
2 Fisica
2 Deportes
como lo podria realizar?,
gracias
Hola Havi, gracias por comunicarte y por tu comentario, si lo quieres hacer es mostrar las materias como columnas, necesariamente tienes que tener una tabla maestra con la codificación de las materias, tal como lo hago con la tabla t_estado_civil, luego con un Union All puedes incluir las materias que tiene cada estudiante haciendo algo como esto:
Tabla t_materia:
id_materia descripcion
1 Calculo
2 Matematicas
3 Fisica
4 Deportes
En la tabla t_estudiante cambias el campo materia por id_materia:
codigoEstudiante id_materia
1 1
1 2
2 3
2 4
Select
‘Materia:’ materia,
max( decode( mat.id_materia, 1, mat.descripcion)) col_1,
max( decode( mat.id_materia, 2, mat.descripcion)) col_2,
max( decode( mat.id_materia, 3, mat.descripcion)) col_3,
max( decode( mat.id_materia, 4, mat.descripcion)) col_4
From t_materia mat
Union all
Select
to_char(est.codigoEstudiante) estudiante,
max( decode( est.id_materia, 1, ‘Si’, ‘No’)) col_1,
max( decode( est.id_materia, 2, ‘Si’, ‘No’)) col_2,
max( decode( est.id_materia, 3, ‘Si’, ‘No’)) col_3,
max( decode( est.id_materia, 4, ‘Si’, ‘No’)) col_4
From t_estudiante est
Group by est.codigoEstudiante;
Saludos
Roberto Vicencio – Para tu Éxito con Oracle.
https://dominatuspackagesen0racle.com/
Hola, muy buena la explicacion, pero tengo una duda, en tu ejemplo muestras las filas como columnas, podrias ayudarme hacendolo a la inversa, osea, quiero que las columas se hagan filas.. tengo un query con :
DESCRIPCION A B C D
HOLA 1 1 1 1
LSDÑLDÑ 2 2 2 2
y busco algo asi
DESCRIPCION PARAMETRO TOTAL
HOLA A 1
HOLA B 1
HOLA C 1
HOLA D 1
y asi sucesivamente, agradeceria tu ayuda.
Gracias
Saludos
Hola Enrique, gracias por comunicarte y por tu comentario, lo que quieres hacer requiere un poco mas de trabajo ya que convertir las columnas en filas es mas complicado hacerlo en una sola consulta select, para hacer esto se requieren usar mas herramientas, como por ejemplo, crear una tabla temporal con la estructura adecuada y llenarla con la información usando un procedimiento almacenado, tambien se puede usar una matriz o estructura tipo tabla en memoria y llenarla con un cursor que lea la tabla de origen, pero todo depende de que es lo que quieres realizar, gracias.
PD: Ya obtuviste el libro-reporte ‘Como Construir un SELECT Eficiente en Oracle en Solo 7 Pasos’, si aún no lo tienes puedes obtenerlo haciendo clic aquí:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Hola. Tengo dos tablas, en una tiene la información de los indices y los nombres de unas variables y en la otra tiene los valores otorgados para esas variables que se recogen cada 5 minutos y necesito un query que me permita que según las variables que un usuario escoja tener las variables por columnas con sus respectivos valores asi:
variable1 variable2 variable3
valor1 valor1 valor1
valor2 valor2 valor2
muchas gracias
Hola Julie, gracias por comunicarte y por tu comentario, por lo que me indicas tu problema calsa muy bien con lo que explico en este post, no conozco tu modelo de datos pero creo que esto te servirá, debes tomar la query del ejemplo #1, para mostrar los nombres de las variables debes reemplazar la tabla t_estado_civil y campos por tu tabla que contiene los nombres de las variables, luego copia nuevamente la query del ejemplo #1 y reemplaza la tabla t_estado_civil y campos por tu tabla que contiene los valores para esas variables, en esta segunda query debes agregar un Group By por el campo id de la variable, el campo hora u otro campo que quieras agrupar, finalmente unes las dos querys con un union all de la siguiente forma:
query1
unon all
query2
Avisame si te puedo ayudar en algo mas.
PD: Ya obtuviste mi libro-reporte gratuito sobre ‘Como Construir un SELECT Eficiente en Oracle en Solo 7 Pasos’, si aún no lo tienes puedes obtenerlo desde aquí:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle
Hola. Gracias por tu respuesta, pero me sale error porque los campos de los dos selects son de diferente tipo. los campos de las variables son tipo varchar y los valores son de tipo float. ORA-01790: expression must have same datatype as corresponding expression. Te agradezco tu ayuda.
Hola Julie, para usar el union all los campos de ambas querys deben ser del mismo tipo y tener la misma cantidad de columnas, el resultado de la segunda query lo debes convertir a varchar usando la función to_char(), gracias.
PD: Ya obtuviste mi libro-reporte gratuito sobre ‘Como Construir un SELECT Eficiente en Oracle en Solo 7 Pasos’, si aún no lo tienes puedes obtenerlo desde aquí:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle
Hola Roberto:
Decías en uno de los comentarios que respondiste a Jorge que para transformar las filas a columnas de una tabla no debe contener miles de filas porque seria algo que no se podría manejar a menos que use SQL Dinámicos pero tendría que crear otra tabla que seria subordinada de la tabla t_estado_civil, entonces esto solo aplicaría mejor para tablas con pocas filas o registro de la tabla o que sean filas con datos estáticos como el ejemplo que nos compartes de la tabla t_estado_civil:
1 SOLTERO
2 CASADO
3 SEPARADO
4 VIUDO
5 OTROS).
Ok, esa es mi primer pregunta?
Segunda pregunta, la tabla que se tiene que crear se arma temporalmente o tiene que estar creada físicamente como la t_estado_civil y sincronizarle los datos para que estén actualizadas las dos tablas con los mismos registros que serian las filas a transformarlas como columnas?
Slds! y gracias, por seguir compartiendo tus conocimientos experiencia.
Hola Fidel, lo que le indico a Jorge es que si la tabla origen tiene demasiadas filas no veo la razon para convertirlas a columnas ya que seria inmanejable, por ejemplo, la tabla t_estado_civil solo tiene 5 filas y al convertirlas a columnas obtengo 5 columnas, pero si tienes una tabla con 1000 filas puedes obtener 1000 o mas columnas lo que no tiene mucho sentido, esto es en cuanto a definir la cantidad de columnas que quieres utilizar como cabecera, en el ejemplo #1 yo utilizo 6 columnas como cabecera, pero en el ejemplo #2 con el Union All le agrego datos a esas 6 columnas obteniendo los datos de la tabla t_empleado, donde agrupo los datos por departamento para obtener la cantidad de personas por cada estado civil, en el ejemplo solo muestro 3 filas (departamentos 101, 102 y 105), pero aqui no hay limite de filas, los datos solo estan limitados por la cantidad de departamentos que existan en la tabla t_empleado.
En cuanto a la segunda pregunta, la tabla se crea temporalmente, ya que se usa un sql dinámico porque no se sabe cuantas son las filas que se van a transformar en columnas para definir la cabecera de los datos.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Muchas gracias muy buena enseñanza.
La estoy utilizando.
Tu blog me parece muy bueno Roberto, muchas gracias por tus aportaciones, en lo particular me han servido de mucho en el trabajo y en un curso que he tomado, buscando la certificación en este ámbito.
se puede hacer tambien en la que los datos no sean numericos
Hola Gibran, gracias por comunicarte, por supuesto que también puedes ocupar esta técnica con datos que no sean numéricos, ya que el truco lo hacen las funciones de grupo como el max, min, count, etc. y estas se pueden aplicar a cualquier tipo de dato, gracias.
PD: Si te gusto este artículo te invito a que compartas este enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les pueda interesar esta información, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito on Oracle.
me interesa este Query , tengo un query en ekl cual tiene 6 columnas que indican un codigo de descuento de un sistema de liquidación y cada codigo en ootra columna donde figura el importe a cada codigo lo que quiero conseguir es que me aparezcan en una sola columna los codigos y en la otra los importe a cada codigos
SELECT a.centrob, a.sectorb, a.claseb, a.numpadb, a.codigo1b, a.imp1b,
a.codigo2b, a.imp2b, a.codigo3b, a.imp3b, a.codigo4b, a.imp4b,
a.codigo5b, a.imp5b, a.codigo6b, a.imp6b, a.letrab, a.codigob,
a.ordenrob, a.dnioperb, a.fecargab, a.codmesb, a.codsisb, a.mes,
a.periodo, a.id_novedades
FROM sigsa_sue.novedad_codigo0314 a
este ejemplo que envías como lo puedo aplicar , gracias !
Que tal Victor, gracias por comunicarte y por tu comentario, por lo que entiendo necesitas convertir columnas a filas, pero este ejemplo es para convertir filas a columnas, lo que quieres hacer requiere un poco mas de trabajo ya que convertir las columnas en filas es mas complicado para hacerlo en una sola consulta select, para hacer eso debes usar mas herramientas, por ejemplo, crear una tabla temporal con la estructura adecuada y llenarla con la información usando un procedimiento almacenado, también puedes usar una estructura tipo tabla en memoria y llenarla con un cursor que lea la tabla origen, gracias.
PD: Si te ha gustado el artículo comparte este enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les puede interesar esta información.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
https://dominatuspackagesen0racle.com/
buenos tips
Buenos días como estas? muy bueno que compartas tus conocimientos, tengo un caso como el siguiente:
Tengo un campo ordenado de la siguiente manera:
JORGE,LUIS,MANUEL
yo quisiera desconcatenarlo y mostrarlo asi:
JORGE
LUIS
MANUEL
Tienes idea?
Gracias.
Hola Jorge, gracias por comunicarte y por tu comentario, bueno dependiendo de donde quieres mostrar el campo es la técnica que debes usar para desconcatenar, si quieres mostrarlo por pantalla o imprimir puedes usar un ciclo For de la forma siguiente:
lst_texto:= ‘JORGE,LUIS,MANUEL,’; — Ojo le agrego siempre una coma al final de la cadena —
lst_nombre:= ‘ ‘;
for pos in 1..lenght(lst_texto)
loop
if substr(lst_texto,pos,1) != ‘,’ then
lst_nombre:= lst_nombre || substr(lst_texto,pos,1); — Concateno el nombre —
else
dbms_output.putline(trim(lst_nombre)); — Se imprime el nombre —
lst_nombre:= ‘ ‘;
end if;
end loop;
PD: Ya obtuviste mi libro-reporte sobre “Cómo Construir un Select Eficiente en Oracle en Solo 7 Pasos”, si aún no lo tienes puedes obtenerlo desde aquí:
https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
hola…Porque no usas el PIVOT, UNPIVOT para transformar las filas en columnas y viceversa?
Hola Robert, gracias por tu comentario, la cláusula PIVOT solo existe desde la versión 11g y a muchos usuarios no les serviría ya que usan versiones anteriores, por eso implemente la solución con la forma tradicional que es igual de efectiva, gracias por tu aporte.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Usar pivot para transformar filas en columnas y UNPIVOT para hacer lo contrario
create table prueba
(
compañia varchar2(50),
mes varchar2(20),
venta number);
select compañia, mes, sum(venta) from prueba
group by compañia, mes
con case
select compañia, junio,julio, agosto, setiembre
from (select compañia,
sum(case when mes=’2008-09′ then venta else null end)setiembre,
sum(case when mes=’2008-08′ then venta else null end)agosto,
sum(case when mes=’2008-07′ then venta else null end)julio,
sum(case when mes=’2008-06′ then venta else null end)junio
from prueba
group by compañia);
Con decode
SELECT compañia,
sum(DECODE(mes, ’2008-06′, venta)) junio,
sum(DECODE(mes, ’2008-07′, venta)) julio,
sum(DECODE(mes, ’2008-08′, venta)) agosto
FROM prueba
GROUP BY compañia
ORDER BY compañía
Clausula PIVOT 11g
SELECT *
FROM ( SELECT compañia, mes, venta
FROM prueba ) S
PIVOT (SUM(venta) FOR mes IN (’2008-06′ as junio,’2008-07′ as julio,’2008-08′ as agosto))
ORDER BY compañia;
Robert, gracias por tu aporte, muy bueno el ejemplo, la diferencia de código entre cada forma no es mucha pero ambas son igual de efectivas, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Hola Roberto, buen tutorial
tengo el siguiente problema
id sexo
1 mujer
1 hombre
1 mujer
y quiero
id Hombre Mujer
1 1 2
Espero me puedas ayudar.
Saludos
Que tal Roberto, gracias por comunicarte, este problema lo puedes resolver de dos formas, aquí te muestro el código:
— Forma tradicional —
select
dat.id,
count(decode(dat.sexo, ‘hombre’, 1)) hombre,
count(decode(dat.sexo, ‘mujer’, 1)) mujer
from ( — aquí debes colocar tu tabla —
select 1 id, ‘mujer’ sexo from dual
union all
select 1 id, ‘hombre’ sexo from dual
union all
select 1 id, ‘mujer’ sexo from dual
) dat
group by
dat.id;
— Si usas la versión 11g o superior puedes usar el PIVOT —
select *
from ( — aquí debes colocar tu tabla —
select 1 id, ‘mujer’ sexo from dual
union all
select 1 id, ‘hombre’ sexo from dual
union all
select 1 id, ‘mujer’ sexo from dual
) dat
pivot
(count(1) for sexo in (‘hombre’ hombre,’mujer’ mujer))
order by id;
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
buenas estimados, soy nuevo en esto hasta el momento esta muy bueno los ejemplos pero mi duda es si el convertir filas en columnas sin la suma, sin contar
quiero decir que yo necesito los datos converidos enten en las columnas no quiero que no lo sume ni que lo cuente
ejemplo de esta manera
Ene Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic
2000 97 10 0 0 73 34 100 0 30 55 20 108
2000 5 10 1 0 50 20 0 10 15 15 20 0
2000 10 1 8 1 10 15 14 10 15 10 0 0
2001 50 0 20 0 60 0 14 0 0 6 20 20
2001 5 10 1 0 50 20 0 10 15 15 20 0
2001 10 1 8 1 10 15 14 10 15 10 0 0
2001 5 10 1 0 50 20 0 10 15 15 20 0
2002 52 20 61 30 35 52 42 60 144 0 99 1
2003 60 0 60 106 35 24 125 30 135 10 65 15
Hola Fenix, gracias por comunicarte, para lograr lo que quieres lo único que debes hacer es quitar en los ejemplos el Group By y las funciones Sum y Count en los campos que deseas mostrar.
PD: Ya obtuviste mi Libro-Reporte sobre “Cómo Crear un SELECT Eficiente en Oracle en Solo 7 Pasos”, si aun no lo tienes puedes obtenerlo desde el enlace: https://dominatuspackagesen0racle.com/, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
ID_VENT FECHA TOTAL ID_CL ID_VE IGV NRO_COMPRO SUBTOTAL DESCUENTO CONCEPTO_VENTA
——- ——– ———- —– —– ———- ———- ———- ———- ———————————–
0001 23/10/14 30,5 007 003 2,4 0000001 32,9 3 Consumo Postres
0002 23/10/14 10,5 002 005 1,4 0000002 11,9 2,3 Consumo Frutas
0003 21/10/14 17,3 009 005 1,9 0000003 19,2 1,8 Consumo Enlatados
0004 29/10/14 80,1 004 007 3,5 0000004 83,6 5 Consumo Golosinas
0005 22/10/14 17,5 001 001 2 0000005 19,5 1,6 Consumo Materiales Estudio
0006 02/10/14 9,1 018 005 1,1 0000006 10,2 1,1 Consumo Carbohidratos
0007 25/10/14 5,5 008 005 ,6 0000007 6,1 ,8 Consumo Frutas
0008 25/10/14 38,5 020 003 2,7 0000008 41,2 3,4 Consumo Carbohidratos
0009 09/10/14 60 013 010 3,9 0000009 63,9 5 Consumo Galletas
0010 21/10/14 57,3 017 002 3,1 0000010 60,4 4,3 Consumo Frutas
0011 02/10/14 130,5 007 001 4,4 0000011 134,9 8,4 Consumo Carbohidratos
0012 22/10/14 23,4 006 010 1,9 0000012 25,3 2,6 Consumo Carbohidratos
0013 02/10/14 10 007 017 1,7 0000013 11,7 1,2 Consumo Frutas
0014 29/10/14 67,5 013 010 4,3 0000014 71,8 4,8 Consumo Postres
0015 25/10/14 56,2 001 002 2,9 0000015 59,1 3,7 Consumo Galletas
0016 16/10/14 23,5 005 003 2 0000016 25 2,3 Consumo Carbohidratos
0017 23/10/14 33,8 003 017 2,9 0000017 36,7 3,4 Consumo Golosinas
0018 23/10/14 87,5 004 003 3,8 0000018 91,3 4,8 Consumo Enlatados
0019 23/10/14 30,7 002 010 2,3 0000019 33 3,1 Consumo Postres
0020 23/10/14 63,5 007 005 3,1 0000020 69,6 4 Consumo Materiales Estudio
—–>MI CONSULTA ES: COMO PUEDO OBTENER EL VENDEDOR QUE ATENDIO A MAS CLIENTES….ES DECIR QUE APAREZCA EL MOMBRE DEL VENDEDOR , Y EL NOMBRE DEL CLIENTE….Por favor, espero su respuesta…
Hola Eliacer, gracias por comunicarte, para resolver este problema te recomiendo que veas el vídeo “Cómo Obtener en un SELECT el Artículo Más Comprado en PL-SQL Packages en Oracle”, donde resuelvo un problema que se parece mucho a lo que indicas, te va ser de mucha ayuda y lo puedes ver en el enlace https://dominatuspackagesen0racle.com/?p=877, gracias.
PD: Ya obtuviste mi libro-reporte sobre “Como Construir un SELECT Eficiente en Oracle en Solo 7 Pasos”, si aun no lo tienes puedes obtenerlo desde el enlace https://dominatuspackagesen0racle.com/.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Extraordinario tema, gracias
Hola buen dia, felicidades por tu pagina….
Tengo una tabla de temperaturas horarias para cada ciudad
ciudad, t1, t2, t3…….t24
A 10,10,12,15,…..23
B 5,5,6,7,………12
C 7,7,8,6,,……..15
D 18,19,20,22……29
Quisiera tener una consulta que me devuelva
Hora A B C D
1 10 5 7 18
2 10 5 7 19
3 12 6 8 20
4 15 7 6 22
.
.
.
24 23 12 15 29
Muchas gracias anticipadamente por tu colaboracion…
Hola Jose, gracias por comunicarte, esta técnica para transformar filas a columnas es eficiente cuando trabajas con tablas codificadas como la t_estado_civil que tiene código y nombre, el ejemplo que muestras es una tabla con campos fijos (t1,t2,t3…) y eso complica hacer la transformación, lo que te recomiendo es crear una tabla temporal con la estructura que quieres mostrar y poblarla con los datos de la tabla original.
PD: Si lo que has visto de este blog te ha gustado, te invito a compartir el enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les pueda interesar este contenido, gracias.
Saludos
Roberto Vicencio – Para tu Éxito con Oracle.
hola y gracias por el tutorial.
El segundo select del Union no esta funcionando 🙁
Select
to_char(emp.id_departamento) departamento,
sum( decode( emp.id_estado_civil, 1, count(1), 0)) cant_col_1,
sum( decode( emp.id_estado_civil, 2, count(1), 0)) cant_col_2,
sum( decode( emp.id_estado_civil, 3, count(1), 0)) cant_col_3,
sum( decode( emp.id_estado_civil, 4, count(1), 0)) cant_col_4,
sum( decode( emp.id_estado_civil, 5, count(1), 0)) cant_col_5
From t_empleado emp
Group by emp.id_departamento, emp.id_estado_civil;
Parece logico que no funcione, ya que en el select hay listado 6 columnas y en el group by solo 2…
Capaz que me perdi en algo…
Saludos.
Hola Pascualino, gracias por comunicarte, el segundo select si funciona, fíjate que de la columna cant_col_1 a la cant_col_5 se usa el mismo campo id_estado_civil para crearlas, por lo tanto en el group by no se deben incluir mas campos.
PD: Ya pudiste ver mi libro reporte sobre ‘Cómo Crear un SELECT Eficiente en ORACLE en Solo 7 Pasos’, si aun no lo tienes en tus manos puedes obtenerlo desde aquí https://dominatuspackagesen0racle.com/, si te ha gustado lo que has visto del blog te invito a compartir el enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les pueda interesar este contenido, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Rectifico mi mensaje anterior respecto al listado dentro del segundo select del union
pero si continua sin funcionar esta concatenacion de funciones de agregado
sum( decode( emp.id_estado_civil, 1, count(1), 0)) cant_col_1
no le gusta nada el count(1) en ese lugar
Gracias!!!
Que tal Pascualino, he revisado detenidamente la segunda query que indicas y si tenias razón, no estaba funcionando, pero ya la he corregido y esta operativa, gracias por tu aporte, esto es lo que ha cambiado:
La columna: sum( decode( emp.id_estado_civil, 1, count(1), 0)) cant_col_1
Cambio por: to_char(sum( decode( emp.id_estado_civil, 1, 1, 0))) cant_col_1
El grupo: Group by emp.id_departamento, emp.id_estado_civil
Cambio por: Group by emp.id_departamento
PD: Ya pudiste ver mi libro reporte sobre ‘Cómo Crear un SELECT Eficiente en ORACLE en Solo 7 Pasos’, si aun no lo tienes en tus manos puedes obtenerlo desde aquí https://dominatuspackagesen0racle.com/, si te ha gustado lo que has visto del blog te invito a compartir el enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les pueda interesar este contenido, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Hola, revisando escribiste acerca del SQL dinámico y pusiste este código, pero no me funciona, me podrías ayudar explicando un poco más como es que funciona?
Muchas gracias!!
lst_sql varchar2(500);
begin
lst_sql:= ‘ ‘;
— Aqui se crea el sql dinámico convirtiendo todas las filas en columnas de la tabla t_estado_civil.
for lin_reg in (select id_estado_civil from t_estado_civil)
loop
lst_sql:= lst_sql || ‘, max( decode( eciv.id_estado_civil, ‘||to_char(lin_reg.id_estado_civil)
||’, eciv.descripcion)) col_’||to_char(lin_reg.id_estado_civil);
end loop;
lst_sql:= lst_sql || ‘ From t_estado_civil eciv’;
— Aqui se ejecuta el sql dinámico para crear la tabla tmp_filas_columnas con las filas convertidas en columnas.
EXECUTE IMMEDIATE (‘create table tmp_filas_columnas as select ”Anexos:” titulo’||lst_sql);
end;
Hola Jussem, gracias por comunicarte, este sql dinámico construye de forma dinámica la misma consulta descrita en el artículo, para que te funcione primero debes tener creada la tabla t_estado_civil tal como se indica en el artículo, al copiar este código en tu editor revisa que la comilla simple sea el caracter correcto de la comilla en pl-sql ya que al copiar y pegar se cambia, para ejecutarlo agrega la palabra declare al principio del script, es decir, debe quedarte algo como esto:
declare
— variables —
begin
— pegar script —
end;
PD: Si te ha gustado lo que has visto del blog te invito a compartir el enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les pueda interesar ver esta información, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Gracias, me fue de mucha ayuda tu aporte.
Un cordial Saludo
Hola Ronal, gracias por comunicarte, me da mucho gusto que esta información te sea de utilidad, ya pudiste ver mi libro reporte sobre ‘Cómo Crear un SELECT Eficiente en ORACLE en Solo 7 Pasos’, si aun no lo tienes en tus manos puedes obtenerlo gratis desde aquí: https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Hola Ulises, mira eso se resuelve en Oracle de la siguiente forma:
SELECT
ID,
WM_CONCAT(Type) Types
FROM TU_TABLA
GROUP BY ID ;
Esto te repetiria los valores iguales, para que no te los repitas, se me ocurre resolverlo así:
SELECT
ID,
WM_CONCAT(Type) Types
FROM (SELECT UNIQUE ID, TYPE FROM TU_TABLA)
GROUP BY ID ;
Hola Alberto, gracias por comunicarte y por tu excelente aporte, si te ha gustado lo que has visto del blog te invito a compartir el enlace https://dominatuspackagesen0racle.com/ con tus amigos o personas que también les pueda interesar ver esta información, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Necesito que las filas que arroje el query queden como columnas, se que se puede hacer con la funcion PIVOT pero no me resulta
select –count (DISTINCT CIG.ID_GARANTIA),
to_char(ai.id_ingreso) ||’-‘||ai.dv_id_ingreso ID_INGRESO,
ai.id_ingreso ID,
GENERALES.TipoGarantia(CG.COD_EMPRESA, CG.COD_FORMAPAGO) TIPO_GARANTIA,
ESTADO EST,
ECONV.RUT_EMPRESA || ‘-‘ || ECONV.DV_EMPRESA RUT_EMPRESA,
ECONV.NOMBRE_EMPRESA NOMBRE
FROM ADM_INGRESOS AI,
CJA_INGRESOS_GARANTIA CIG, CJA_GARANTIA CG, CON_EMPRESA ECONV
where CG.COD_EMPRESA = ECONV.COD_EMPRESA (+)
AND CG.EMPRESA_CONVENIO = ECONV.EMPRESA_CONVENIO (+)
AND AI.COD_EMPRESA = CIG.COD_EMPRESA (+)
AND AI.COD_SUCURSAL = CIG.COD_SUCURSAL (+)
AND AI.ID_INGRESO = CIG.ID_INGRESO (+)
AND CIG.COD_EMPRESA = CG.COD_EMPRESA (+)
AND CIG.COD_SUCURSAL = CG.COD_SUCURSAL (+)
AND CIG.ID_GARANTIA = CG.ID_GARANTIA (+)
and AI.fecha_ingreso >= TO_DATE(’20/12/2016′,’DD/MM/YYYY’)
and AI.fecha_ingreso < TO_DATE('01/04/2017','DD/MM/YYYY')
and COD_ESTADO_INGRESO ‘ANU’
AND ESTADO ‘ANU’
Hola Cristina, gracias por comunicarte, el PIVOT no permite usar columnas concatenadas y funciones en sus columnas, solo puedes usar columnas individuales, pero para solucionarlo usa una sub-query como lo siguiente:
select *
from (
Select
col1 col_a,
col2||’-‘||col3 col_b,
nvl(col4,0) col_c
From Tu_tabla ) a
Pivot
(Count(col_a) For col_c In (1 Col_1, 2 Col_2))
PD: Ya viste mi libro-reporte sobre “Cómo Crear un SELECT Eficiente en ORACLE en Solo 7 Pasos”, si aun no lo tienes en tus manos puedes obtenerlo gratis desde aquí: https://dominatuspackagesen0racle.com/
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
HEADER_ID CHARGE_TYPE AMOUNT
20842 D6F 1.00
20842 ECC 2.81
20842 ECO 8.44
20842 SLC 2.47
** NECESITO UN SOLA FILA PARA ESTOS REGISTROS Y LUEGO SUMAR LOS AMOUNT; ASI:
HEADER_ID AMOUNT1 AMOUNT2 AMOUNT3 AMOUNT4 AMOUNT_TTL
20842 1.00 2.81 8.44 2.47 14.72
Hola Carlos, gracias por comunicarte, si te fijas use la misma lógica descrita en el artículo, ahora solo debe poner tu tabla:
select
dat.header_id,
max(decode(dat.charge_type, ‘D6F’, dat.amount)) D6F,
max(decode(dat.charge_type, ‘ECC’, dat.amount)) ECC,
max(decode(dat.charge_type, ‘ECO’, dat.amount)) ECO,
max(decode(dat.charge_type, ‘SLC’, dat.amount)) SLC,
sum(dat.amount) total
from ( –— aquí debes colocar tu tabla —
select 20842 header_id, ‘D6F’ charge_type, 1.00 amount from dual
union all
select 20842 header_id, ‘ECC’ charge_type, 2.81 amount from dual
union all
select 20842 header_id, ‘ECO’ charge_type, 8.44 amount from dual
union all
select 20842 header_id, ‘SLC’ charge_type, 2.47 amount from dual
) dat
group by
dat.header_id;
PD: Ya viste mi guía sobre “Cómo Crear SELECT Eficientes en ORACLE en Solo 7 Pasos”, si aun no la tienes en tus manos puedes obtenerla gratis desde aquí: https://dominatuspackagesen0racle.com/cmrz
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Que tal use la query de arriba pero me sale este error
” Error Code: 1582. Incorrect parameter count in the call to native function ‘decode’ ” a que se debe??
Hola Jesus, gracias por comunicarte, debes revisar que la query este escrita de forma correcta, es decir, que no te falte una coma, una letra o paréntesis, ya que estas querys han sido probadas y funcionan correctamente.
PD: Ya viste mi guía sobre “Cómo Crear SELECT Eficientes en ORACLE en Solo 7 Pasos”, si aun no la tienes en tus manos puedes obtenerla gratis desde aquí: https://dominatuspackagesen0racle.com/cmrz
PD2: Si te ha gustado lo que has visto del blog te invito a compartir el enlace https://dominatuspackagesen0racle.com/cmbl/ con tus amigos o personas que también les pueda interesar ver esta información, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
Que tal…. no se si me pudiera ayudar con una consulta que no puedo realizar…. tengo todas las fechas de un determinado mes pero las fechas las saca de forma vertical y quiero obtenerlas de forma horizontal o mejor dicho que sean las cabeceras de la consulta.
Esta es:
SELECT TO_DATE (’01-‘ || ‘&Mes’ || ‘-‘ || ‘&Año’, ‘DD-MM-YYYY’)+ LEVEL- 1 AS dias_del_mes
FROM DUAL
CONNECT BY LEVEL <= EXTRACT (day FROM last_day('01-' || '&Mes' || '-' || '&Año'));
Hola Jesus, gracias por comunicarte, para dejar los días como columnas puedes usar el pivot de la siguiente forma:
select *
from (
SELECT
TO_DATE (’01-‘ || :Mes || ‘-‘ || :Año, ‘DD-MM-YYYY’)+ LEVEL- 1 AS dias_del_mes,
to_number(to_char(TO_DATE (’01-‘ || :Mes || ‘-‘ || :Año, ‘DD-MM-YYYY’)+ LEVEL- 1,’dd’)) AS dia
FROM DUAL
CONNECT BY LEVEL <= EXTRACT (day FROM last_day('01-' || :Mes || '-' || :Año)) ) dat Pivot (Max(dias_del_mes) For dia In (1 d1,2 d2,3 d3,4 d4,5 d5,6 d6,7 d7,8 d8,9 d9,10 d10,11 d11,12 d12,13 d13,14 d14,15 d15,16 d16, 17 d17,18 d18,19 d19,20 d20,21 d21,22 d22,23 d23,24 d24,25 d25,26 d26,27 d27,28 d28,29 d29,30 d30,31 d31)) PD: Si te ha gustado lo que has visto del blog te invito a compartir el enlace https://dominatuspackagesen0racle.com/cmbl/ con tus amigos o personas que también les pueda interesar ver esta información, gracias.
Saludos
Roberto Vicencio – Para Tu Éxito con Oracle.
CON QUE FINALIDAD USO UNA TABLA DINAMICA SI LA INFORMACION IGUALMENTE LA PUEDO MANEJAR DESDE UN CURSOR Y AMBOS TIENEN LA DATA EN LA MEMORIA.
CUANDO USAR EL CURSOR Y CUANDO USAR UNA TABLA DINAMICA ?
Hola que tal Roberto, buen tutorial, tengo una consulta, como puedo hacer cuando tengo mas estados, y se necesiten agregar mas decode, en conclusion hacerlo de una manera dinamica en oracle, se podra hacer eso?