Sayfalar

29 Haziran 2011 Çarşamba

PL/SQL de satırdan sutuna , sutundan satıra çevirme, crosstab , row to column , column to row

SELECT u.user_name
  FROM fnd_user u
 WHERE u.creation_date > SYSDATE 120

Şeklinde yazılan sorguda sonuç

Ahmet
Mehmet
Veli

Olsun. Amacımız oluşan bu listeyi tek satirda araya bizim belirleyeceğimiz bir ayraç ile birleştirerek

Ahmet,Mehmet,Veli, ….

gibi getirmek ise şu yöntemi uygulayabiliriz…


Öncelikle liste için bir tip belirleriz…

CREATE TYPE TListe IS TABLE OF VARCHAR2 (4000);

Bir de bu listeyi belirleyeceğimiz ayraç ile birleştiren fonksiyonu oluştururuz…

CREATE FUNCTION apps.birlestir (p_liste IN tliste, p_separator IN VARCHAR2)
   RETURN VARCHAR2 IS
   ret   VARCHAR2 (4000);
BEGIN
   FOR j IN 1 .. p_liste.LAST LOOP
      IF j = 1 THEN
         ret := p_liste (j);
      ELSE
         ret := ret || p_separator || p_liste (j);
      END IF;
   END LOOP;

   RETURN ret;
EXCEPTION
   WHEN OTHERS THEN
      RETURN NULL;
END;

Şimdi sorgumuza geçebiliriz…

SELECT apps.birlestir (CAST (MULTISET (SELECT u.user_name
                                         FROM fnd_user u
                                        WHERE u.creation_date > SYSDATE - 120) AS apps.tliste),
                       ',')
          isimler
FROM DUAL;




Sonuç :

isimler
Ahmet,Mehmet,Veli,…

Olur…

Satirdan sutuna geçirmenin diger bir yöntemi de

SELECT TRUNC (f.creation_date) tarih, RTRIM (XMLAGG (XMLELEMENT (e, f.user_name || '-' || f.user_id || ',')).EXTRACT ('//text()'), ',') isimler
  FROM fnd_user f
GROUP BY TRUNC (creation_date);

Tarih              isimler
01.01.2011    Ahmet-1,Mehmet-2
01.02.2011    Veli-3, Mustafa-4

Gibi sonuclar olacaktir…



Ayraç ile birleştirilmiş bir alandan istenen parçaların getirilmesinin sağlanmasi

Örneğin;

Ne 30/1 - 12630037%76|100/36 den - 101721%24

Yukaridaki alanda ayraç “|” olarak belirlenmiş..
Biz bu ayraca gore
Ne 30/1 - 12630037%76
Ve
100/36 den - 101721%24
Değerlerinin alınmasını istersek su fonksiyonu yazabiliriz…


CREATE OR REPLACE FUNCTION APPS.explode (
   p_seperator   IN   VARCHAR2,
   p_string      IN   VARCHAR2,
   p_count            NUMBER
)
   RETURN VARCHAR2
AS
   l_string   LONG           DEFAULT p_string || p_seperator;
   TYPE listtable is TABLE OF VARCHAR2 (255);
   l_data     listtable      := listtable ();
   n          NUMBER;
   RESULT     VARCHAR2 (255);
BEGIN
   LOOP
      EXIT WHEN l_string IS NULL;
      n := INSTR (l_string, p_seperator);
      l_data.EXTEND;
      l_data (l_data.COUNT) := LTRIM (RTRIM (SUBSTR (l_string, 1, n - 1)));
      l_string := SUBSTR (l_string, n + 1);
   END LOOP;

   RESULT := l_data (p_count);
   RETURN RESULT;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN RESULT;
END;


SELECT 'Ne 30/1 - 12630037%76|100/36 den - 101721%24' orj, explode ('|', 'Ne 30/1 - 12630037%76|100/36 den - 101721%24', 1) ilk, explode ('|', 'Ne 30/1 - 12630037%76|100/36 den - 101721%24', 2) ikinci
  FROM DUAL;

Çıktı :

Orj                                                                                              ilk                                                                       ikinci
Ne 30/1 - 12630037%76|100/36 den - 101721%24              Ne 30/1 - 12630037%76 100/36 den - 101721%24

Bu örneği bir üst level a alirsak…
Ayraç ile ayırmanın en fazla 8 adet olabileceğini bilerek eklenen herbir değeri satir satir göstermek isteyelim…

SELECT ROWNUM, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 1) ilk, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 2) iki,
               explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 3) uc, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 4) dort, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 5) bes,
               explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 6) alti, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 7) yedi, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 8) sekiz
          FROM dual k
Bu sorgu ile tek satıra düşüyoruz…

SELECT ROWNUM,CASE
          WHEN ROWNUM = 1 THEN ilk
          WHEN ROWNUM = 2 THEN iki
          WHEN ROWNUM = 3 THEN uc
          WHEN ROWNUM = 4 THEN dort
          WHEN ROWNUM = 5 THEN bes
          WHEN ROWNUM = 6 THEN alti
          WHEN ROWNUM = 7 THEN yedi
          WHEN ROWNUM = 8 THEN sekiz
       END bilgi
  FROM (SELECT ROWNUM, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 1) ilk, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 2) iki,
               explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 3) uc, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 4) dort, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 5) bes,
               explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 6) alti, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 7) yedi, explode ('|', 'TEKSTURE - 3R28884%7,31|TEKSTURE - 010015504KREM01%25,27|TEKSTURE - 010007255BEJ01%4,65|TEKSTURE - 3R28978%16,46|TEKSTURE - 3R28978%4,3|TEKSTURE - 3R2', 8) sekiz
          FROM dual k)
CONNECT BY LEVEL <= 8


Çıktı:
1              TEKSTURE - 3R28884%7,31
2              TEKSTURE - 010015504KREM01%25,27
3              TEKSTURE - 010007255BEJ01%4,65
4              TEKSTURE - 3R28978%16,46
5              TEKSTURE - 3R28978%4,3
6              TEKSTURE - 3R2
7             
8             

Şeklinde olur…


Procedure ise 

CREATE FUNCTION APPS.explode (

   p_seperator   IN   VARCHAR2,
   p_string      IN   VARCHAR2,
   p_count            NUMBER
)
   RETURN VARCHAR2
AS
   l_string   LONG           DEFAULT p_string || p_seperator;
   TYPE listtable is TABLE OF VARCHAR2 (255);
   l_data     listtable      := listtable ();
   n          NUMBER;
   RESULT     VARCHAR2 (255);
BEGIN
   LOOP
      EXIT WHEN l_string IS NULL;
      n := INSTR (l_string, p_seperator);
      l_data.EXTEND;
      l_data (l_data.COUNT) := LTRIM (RTRIM (SUBSTR (l_string, 1, n - 1)));
      l_string := SUBSTR (l_string, n + 1);
   END LOOP;

   RESULT := l_data (p_count);
   RETURN RESULT;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN RESULT;
END;










Random veriye ihtiyaç duyulursa

SELECT     rownum,TRUNC (DBMS_RANDOM.VALUE (1, 100)) a,
           DBMS_RANDOM.STRING ('U', 20) b, DBMS_RANDOM.STRING ('L', 20) c,
           DBMS_RANDOM.STRING ('A', 20) d, DBMS_RANDOM.STRING ('P', 20) e,
           DBMS_RANDOM.VALUE (1, 100) f, DBMS_RANDOM.VALUE g,
           DBMS_RANDOM.random h
      FROM DUAL
CONNECT BY LEVEL <= 7

U - Upper case
L - Lower case
A - Alphanumeric
X - Alphanumeric with upper case alphabets.
P - Printable characters only.

--------



SELECT DISTINCT k.ad, wm_concat (DISTINCT k.soyad) soyadlar, COUNT (DISTINCT k.soyad)
  FROM sevk.kullanici k
GROUP BY k.ad
ORDER BY COUNT (DISTINCT k.soyad) DESC


wm_concat kullanarakta yukarıdaki gibi bir sorgu çalıştırılabilir...

Hiç yorum yok: