sql - multiple - pivot oracle columns to rows

"Pivotar" una tabla en SQL(es decir, tabulación cruzada/tabla de contingencia)

Question

Estoy trabajando para tratar de generar un informe a partir de un par de tablas de la base de datos. La versión simplificada se ve así

Campaign
----------
CampaignID

Source
-----------------------
Source_ID | Campaign_ID

Content
---------------------------------------------------------
Content_ID | Campaign_ID | Content_Row_ID | Content_Value

El informe debe leerse así:

CampaignID - SourceID - ContentRowID(Value(A)) - ContentRowID(Value(B))

Donde ContentRowID (Valor (A)) significa "Encontrar una fila que tiene un CampaignID dado, y un ContentRowId de" A "y luego obtener ContentValue para esa fila"

Esencialmente, tengo que "pivotar" (creo que ese es el término correcto) las filas en columnas ...

Es una base de datos Oracle 10g ...

¿Alguna sugerencia?

Answer 1

Esta es mi primera puñalada. Refinamiento que vendrá una vez que sepa más sobre los contenidos de la tabla de contenido.

Primero, necesitas una tabla temporal:

CREATE TABLE pivot (count integer);
INSERT INTO pivot VALUES (1);
INSERT INTO pivot VALUES (2);

Ahora estamos listos para consultar.

SELECT campaignid, sourceid, a.contentvalue, b.contentvalue
FROM content a, content b, pivot, source
WHERE source.campaignid = content.campaignid
AND pivot = 1 AND a.contentrowid = ''A''
AND pivot = 2 AND b.contentrowid = ''B''

Answer 2

Para hacer esto en SQL estándar, necesita conocer todos los valores distintivos de Content_Row_ID, y hacer una unión por valor distinto. Entonces necesita una columna por valor distinto de Content_Row_ID.

SELECT CA.Campaign_ID, 
  C1.Content_Value AS "39100",
  C2.Content_Value AS "39200",
  C3.Content_Value AS "39300"
FROM Campaign CA
  LEFT OUTER JOIN Content C1 ON (CA.Campaign_ID = C1.Campaign_ID 
    AND C1.Content_Row_ID = 39100)
  LEFT OUTER JOIN Content C2 ON (CA.Campaign_ID = C2.Campaign_ID 
    AND C2.Content_Row_ID = 39200)
  LEFT OUTER JOIN Content C3 ON (CA.Campaign_ID = C3.Campaign_ID 
    AND C3.Content_Row_ID = 39300);

A medida que aumenta el número de valores distintos, esta consulta se vuelve demasiado costosa para ejecutarse de manera eficiente. Probablemente sea más fácil buscar los datos de forma más sencilla y reformatearlos en PL / SQL o en el código de la aplicación.

Answer 3

Si necesita un número dinámico de columnas, no creo que esto se pueda hacer en SQL estándar que, lamentablemente, excede mi conocimiento. Pero hay características de Oracle que pueden hacerlo. Encontré algunos recursos:

http://www.sqlsnippets.com/en/topic-12200.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063#41097616566309

Answer 4

Si no tiene un número dinámico de columnas y su conjunto de datos no es demasiado grande, podría hacer esto ...

SELECT CampaignID, SourceID, 
   (SELECT Content_Value FROM Content c 
      WHERE c.Campaign_ID=s.Campaign_ID 
      AND Content_Row_ID = 39100 
      AND rownum<=1) AS Value39100,
   (SELECT Content_Value FROM Content c 
      WHERE c.Campaign_ID=s.Campaign_ID 
      AND Content_Row_ID = 39200 
      AND rownum<=1) AS Value39200
FROM Source s;

Repita la subconsulta para cada Content_Row_ID adicional.

Answer 5

Si tiene "Oracle, la referencia completa", busque una sección titulada "Convirtiendo una mesa en su lado". Esto proporciona ejemplos detallados e instrucciones para realizar un pivote, aunque la edición que tengo no lo llama pivote.

Otro término para "pivotar una tabla" es la tabla de contingencia.

Una de las herramientas más fáciles de usar para realizar la tabla de contingencia es MS Access. Si tiene MS Access y puede establecer un enlace de tabla desde una base de datos de Access a su tabla de origen, ya está a medio camino.

En ese momento, puede subir el "Query Wizard" y pedirle que cree una consulta cruzada para usted. Realmente es tan fácil como responder las preguntas que el asistente le hace. El lado desafortunado de esta solución es que si observa la consulta resultante en la vista de SQL, verá algunos SQL que son peculiares del dialecto de acceso de SQL, y no se pueden usar, en general, en otras plataformas.

También puede descargar algunas herramientas de análisis simples del sitio web de Oracle y utilizar una de esas herramientas para realizar una tabla de contingencia cruzada.

Una vez más, si realmente desea hacerlo en SQL, "Oracle, la referencia completa" debería ayudarlo.

Answer 6

Bill Karwin menciona esto, pero creo que esto merece ser destacado muy claramente:

SQL no hace lo que está pidiendo, por lo que cualquier "solución" que obtenga será un desafío.

Si sabes , con certeza, siempre se ejecutará en un Oracle 10, entonces seguro, la tabla de contingencia de Walter Mitty podría hacerlo. La forma correcta de hacerlo es trabajar la combinación más fácil de orden en el código de consulta y aplicación para establecerla correctamente.

• Funciona en otros sistemas de bases de datos,
• no se arriesga a que aparezcan otras capas (recuerdo que MySQL tiene un problema con> 255 columnas, por ejemplo. ¿Está seguro de que hace una interfaz entre las bibliotecas y el DB mismo?)
• es (por lo general) no mucho más difícil.

Si es necesario, simplemente puede solicitar los Content_Row_ID primero, luego pregunte por las filas que necesite, ordenadas por CampaignID , ContentRowID , que le proporcionarían cada celda (poblada) de izquierda a derecha, línea por línea. orden.

PD.

Hay muchas cosas que el hombre moderno piensa que SQL debería tener / hacer, que simplemente no existen. Este es uno, los rangos generados son otro cierre recursivo, ORDER BY , lenguaje de programación estandarizado ... la lista continúa. (aunque, ciertamente, hay un truco para ORDER BY )

Answer 7

Bill Karwin y Anders Eurenius tienen razón al decir que no existe una solución sencilla, ni existe ninguna solución cuando el número de valores de columnas resultantes no se conoce de antemano. Oracle 11g sí lo simplifica un poco con el operador PIVOT , pero las columnas aún deben conocerse con antelación y no cumplen los criterios 10g de su pregunta.

Answer 8

Hice una solución con este SQL. Necesité que las filas sean el número de clases y las columnas sean el resumen de cada classe por mes, por lo tanto, la primera columna es el resto de la fila y cada una de las columnas es el resumen de cada mes, y la última fila es el resumen de columna completa mes por mes.

Buena suerte

Select DS.Cla,
Sum(case
when (Extract(year from DS.Data) =:intYear) then DS.PRE
else 0
end) as ToTal,
Sum(case
when (Extract(month from DS.Data) =1) then DS.PRE
else 0
end) as Jan,
Sum(case
when (Extract(month from DS.Data) =2) then DS.PRE
else 0
end) as FEV,
Sum(case
when (Extract(month from DS.Data) =3) then DS.PRE
else 0
end) as MAR,
Sum(case
when (Extract(month from DS.Data) =4) then DS.PRE
else 0
end) as ABR,
Sum(case
when (Extract(month from DS.Data) =5) then DS.PRE
else 0
end) as MAI,
Sum(case
when (Extract(month from DS.Data) =6) then DS.PRE
else 0
end) as JUN,
Sum(case
when (Extract(month from DS.Data) =7) then DS.PRE
else 0
end) as JUL,
Sum(case
when (Extract(month from DS.Data) =8) then DS.PRE
else 0
end) as AGO,
Sum(case
when (Extract(month from DS.Data) =9) then DS.PRE
else 0
end) as SETE,
Sum(case
when (Extract(month from DS.Data) =10) then DS.PRE
else 0
end) as OUT,
Sum(case
when (Extract(month from DS.Data) =11) then DS.PRE
else 0
end) as NOV,
Sum(case
when (Extract(month from DS.Data) =12) then DS.PRE
else 0
end) as DEZ
from Dados DS
Where DS.Cla > 0
And Extract(Year from DS.Data) = :intYear
group by DS.CLA

Union All

Select 0*count(DS.cla),  0*count(DS.cla),
Sum(case
when (Extract(month from DS.Data) =1) then DS.PRE
else 0
end) as JAN,
Sum(case
when (Extract(month from DS.Data) =2) then DS.PRE
else 0
end) as FEV,
Sum(case
when (Extract(month from DS.Data) =3) then DS.PRE
else 0
end) as MAR,
Sum(case
when (Extract(month from DS.Data) =4) then DS.PRE
else 0
end) as ABR,
Sum(case
when (Extract(month from DS.Data) =5) then DS.PRE
else 0
end) as MAI,
Sum(case
when (Extract(month from DS.Data) =6) then DS.PRE
else 0
end) as JUN,
Sum(case
when (Extract(month from DS.Data) =7) then DS.PRE
else 0
end) as JUL,
Sum(case
when (Extract(month from DS.Data) =8) then DS.PRE
else 0
end) as AGO,
Sum(case
when (Extract(month from DS.Data) =9) then DS.PRE
else 0
end) as SETE,
Sum(case
when (Extract(month from DS.Data) =10) then DS.PRE
else 0
end) as OUT,
Sum(case
when (Extract(month from DS.Data) =11) then DS.PRE
else 0
end) as NOV,
Sum(case
when (Extract(month from DS.Data) =12) then DS.PRE
else 0
end) as DEZ
from Dados DS
Where DS.Cla > 0
And Extract(Year from DS.Data) = :intYear

Answer 9

Si no conoce el número de columnas en el frente simplemente devuelva una consulta sql normal y use el código del lado del servidor como he enumerado aquí: Llenando la cuadrícula de datos y la consulta SQL