PL/SQL’de Satırdan Sütuna , Sütundan Satıra Gösterim(Crosstab, Row to Column, Column to Row)

[code lang=”sql”] SELECT u.user_name
FROM fnd_user u
WHERE u.creation_date > SYSDATE – 120
[/code]

Ş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…

[code lang=”sql”] CREATE TYPE TListe IS TABLE OF VARCHAR2 (4000);
[/code]

Bir de bu listeyi belirleyeceğimiz ayraç ile birleştiren fonksiyonu oluştururuz…
[code lang=”sql”] 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;
[/code]

Şimdi sorgumuza geçebiliriz…

[code lang=”sql”] 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;
[/code]

Sonuç :

isimler
Ahmet,Mehmet,Veli,…

Olur…

Satirdan sutuna geçirmenin diger bir yöntemi de

[code lang=”sql”] 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);
[/code]

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…

[code lang=”sql”] 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;
[/code] [code lang=”sql”] 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’,
[/code]

2) ikinci
[code lang=”sql”]FROM DUAL;[/code]

Çı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…

Çı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
[code lang=”sql”] 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;
[/code]

Random veriye ihtiyaç duyulursa
[code lang=”sql”] 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
[/code]

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

——–
[code lang=”sql”] 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
[/code]