Emrah AÇIKGÖZ'ün Kişisel Sahifesi.

Oracle de hızlıca % hesaplama ve pivot

  • 4 sene önce, Emrah tarafından yazılmıştır.
  • 0 Yorum
  • Oracle

ÖRNEK;

SELECT TMP2.*, ROUND((“‘<10 sn'” *100 )/ TOPLAM) AS “(%)'<10 sn'” ,ROUND((“’10-15 sn'” *100 )/ TOPLAM) AS “(%)’10-15 sn'”,ROUND((“’16-30 sn'” *100 )/ TOPLAM) AS “(%)’16-30 sn'”,
ROUND((“’31-60 sn'” *100 )/ TOPLAM) AS “(%)’31-60 sn'” ,ROUND((“’61-120 sn'” *100 )/ TOPLAM) AS “(%)’61-120 sn'”,ROUND((“‘121-180 sn'” *100 )/ TOPLAM) AS “(%)’121-180 sn'”,
ROUND((“‘181-240 sn'” *100 )/ TOPLAM) AS “(%)’181-240 sn'”,ROUND((“‘241-300 sn'” *100 )/ TOPLAM) AS “(%)’241-300 sn'”, ROUND((“‘301-400 sn'” *100 )/ TOPLAM) AS “(%)’301-400 sn'”,
ROUND((“‘401-500 sn'” *100 )/ TOPLAM) AS “(%)’401-500 sn'” , ROUND((“‘>501 sn'” *100 )/ TOPLAM) AS “(%)’>501 sn'”
FROM
(
WITH TMP1 AS
BU ALANA SORGUNUZ GELECEK
)
SELECT SICIL,ISIM, YNTM, gun, “‘<10 sn'”,”’10-15 sn'”,”’16-30 sn'”,”’31-60 sn'”,”’61-120 sn'”,”‘121-180 sn'”,”‘181-240 sn'”, “‘241-300 sn'”,”‘301-400 sn'”,”‘401-500 sn'”,”‘>501 sn'”, “‘<10 sn'” + “’10-15 sn'” + “’16-30 sn'” + “’31-60 sn'” + “’61-120 sn'” + “‘121-180 sn'” +”‘181-240 sn'”+ “‘241-300 sn'” + “‘301-400 sn'” + “‘401-500 sn'” +”‘>501 sn'” AS TOPLAM FROM TMP1
GROUP BY SICIL,ISIM, YNTM, gun, “‘<10 sn'”,”’10-15 sn'”,”’16-30 sn'”,”’31-60 sn'”,”’61-120 sn'”,”‘121-180 sn'”,”‘181-240 sn'”, “‘241-300 sn'”,”‘301-400 sn'”,”‘401-500 sn'”,”‘>501 sn'”
) TMP2 ORDER BY SICIL

Oracle’da pivot nasıl yapılır?

  • 4 sene önce, Emrah tarafından yazılmıştır.
  • 0 Yorum
  • Oracle

Oracle’da basitce pivot işlemini nasıl yapabiliriz?

Örnek:
SELECT * FROM(sorgunuz)
PIVOT(COUNT(SURE) FOR (SURE) IN (‘<10 sn’,’10-15 sn’,’16-30 sn’,’31-60 sn’,’61-120 sn’,’121-180 sn’,’181-240 sn’, ‘241-300 sn’,’301-400 sn’,’401-500 sn’,’>501 sn’))

Öncesi;
oracle_pivot_1

 

 

 

Sonrası;
oracle_pivot_2