String Aggregation in Oracle SQL using XMLAGG

Requirement

We have table test5 with following data
c1
-----
ADD
DELETE
MODIFY

Required output
col
------
ADD,DELETE.MODIFY

i.e we want column value to be aggregated with comma.

Solution
In real scenario we will have another column on which we need to group and then aggregate corresponding values.

In oracle 11g there is one function wm_concat which concatenates column values with comma. We can write a procedure to perform this thing but here we will  try  to do it with sql query alone.

This solution uses XMLELEMENT, XML_AGGR functions

[sourcecode language="sql"]
SELECT replace (replace (replace (XMLELEMENT ("col" , XMLAGG(XMLELEMENT("col", c1))), '</col><col>' , ',' ),'<col><col>' , '' ),'</col></col>', '')
as "col"  FROM test5
[/sourcecode]

Output
col
------
ADD,DELETE,MODIFY

Dynamic column name in Oracle SQL Query using dbms_xmlgen

Consider scenario where we need to use dynamic column name in query.
You might have tried using select query in place of column list something like

select (select distinct column_name from table1 where column_name2='some_value') from table2

Based on some condition we will select column name from one table(test3 table here) and query another table(test 4) for the fetched column.

Consider table test4 with following data

[sourcecode language="sql"]
create table test4(c1 varchar2(20), c2 varchar2(20), c3 varchar2(20))
[/sourcecode]

c1                  c2         c3             c4
-----------------------------------------------
col1_val   col2_val   col3_val   col4_val

And another table test3 with column names of test4  as rows here.

[sourcecode language="sql"]
create table test3(col_name varchar2(2), flag number)
[/sourcecode]

col_name   flag
-------------------
c1                0
c2                1
c3                0

based on flag value we want to select the column name. We want to achieve something like

select (select col_name from test3 where flag = 1) from test4  [this doesn't works]

We will use oracle xml functions getxml and extractvalue to achieve result. dbms_xmlgen.getxml takes SQL query as its argument and returns result in xml format.This function allows us to dynamically create sql query, we can use dynamic column or table name in it.

[sourcecode language="sql"]
select to_char (extractvalue (xmltype (dbms_xmlgen.getxml('select '||
to_char(extractvalue(xmltype(dbms_xmlgen.getxml('select col_name  from test3 where flag =1')),'/ROWSET/ROW/COL_NAME')) || ' col from test4'
)),'/ROWSET/ROW/COL')) col_value from dual[/sourcecode]

SQL Problem: Item falls in which group boolean result

Scenario
Consider scenario where students data is present in two tables

1) Students in A group have entry in table A_group
  student_id
101
102
103
108

2)Students in B group have entry in table B_group

  student_id
101
103
109
111
Condition : Student can belong to either or both groups.

Requirement

Result is required in the form

student_id    A_group_flag  B_group_flag

101                      1               1
102                      1               0
103                      1               1
108                      1               0
109                      0               1
111                      0               1

which shows student_id and Boolean values indicating if the student is present in that group or not.

*flag value 1 indicates student is present in that group

*flag value 0 indicates student is not present in that group

Solution

Here is one of possible solution that I thought of on first shot

Step 1 :

First we combine results from both tables without filtering any

SELECT DISTINCT student_id, 1 AS flag
FROM   a_group
UNION ALL
SELECT DISTINCT student_id, 4 AS flag
FROM   b_group

This query will give all the student ids from both tables with a flag indicating student's group

*flag value 1 indicates A group
flag value 4 indicates B group

Step 2 :
Now we have data of the form

student_id   flag

101             1

102             1

103             1

108             1

101             4
103             4
109             4
111             4

Now comes the logic part
If we group data by student_id and perform aggregate sum on flag like

SELECT student_id,
SUM(flag) AS newflag
FROM   (SELECT DISTINCT student_id,  1 AS flag
FROM   a_group
UNION ALL
SELECT DISTINCT student_id, 4 AS flag
FROM   b_group)
GROUP BY student_id

We will get following values of new flag

1  for students present in A group
4  for students present in B group
5 for student present in both groups

Result of above query would be of form

student_id   newflag
101                5
102                1
103                5
108                1
109                4
111                4

Step 3 :
Next we need to use 'IF conditionals'  to show required data with query like

SELECT student_id,
( CASE
WHEN newflag = 1
OR newflag = 5 THEN 1
ELSE 0
END ) a_group_flag,
( CASE
WHEN newflag = 4
OR newflag = 5 THEN 1
ELSE 0
END ) b_group_flag
FROM
(SELECT student_id,
SUM(flag) AS newflag
FROM   (SELECT DISTINCT student_id,  1 AS flag
FROM   a_group
UNION ALL
SELECT DISTINCT student_id, 4 AS flag
FROM   b_group)
GROUP BY student_id )

This would give us required result of the form

student_id  A_group_flag  B_group_flag
101                 1                                  1
102                 1                                 0
103                 1                                 1
108                 1                                 0
109                 0                                 1
111                 0                                  1

Session variable and Context in Oracle, Using dbms_session.set_context

Oracle  'Context'  can be used to capture session variables

• A context is associated with a package and in that package's procedure we can use this context


• It holds attribute value pair.


• Attribute value is set using DBMS_SESSION.SET_CONTEXT


• Attribute value can be set inside that package only to which it is associated.


• Attribute value can be queried with 'sys_context' function.

Let's test it

Step 1:

For creating and using context we would need below privileges

GRANT CREATE SESSION, CREATE ANY context TO USER; 

Step 2:

create a context 'c_userAttr' associated with package 'p_userAttr'

Syntax
create context context-namespace using package-name;

CREATE context c_userattr USING p_userattr; 

Step 3:
Now we will create package definition

CREATE OR replace PACKAGE p_userattr
IS
PROCEDURE set_contexts;
END; 

Write package body that sets the attribute for context

We can set as many attributes we want using DBMS_SESSION.SET_CONTEXT statement
In this case 'username' is the attribute we will set here

CREATE OR replace PACKAGE BODY p_userattr
IS
c_context CONSTANT VARCHAR2(30) := 'c_userAttr';
PROCEDURE Set_contexts(v_uname IN VARCHAR2)
IS
BEGIN
dbms_session.Set_context (c_context, 'username', v_uname);
END;
END; 

Last Step

In application we can set context with procedure call like this

BEGIN
p_userattr.Set_contexts('crazy');
END; 
and in the same session we can query value in that context namespace like

SELECT Sys_context('C_USERATTR', 'USERNAME')
FROM   dual; 

Variable Table name in Oracle SQL using dbms_xmlgen

Requirement

To find count of all tables

The interesting stuff in oracle is not just writing queries that preforms the required task but to write some single liners that could do the same work as lengthy procedures

so another requirement is to make it interesting

Solution

Solution 1

A procedural way to achieve this through looping for cursor is
DECLARE
CURSOR c1 IS
SELECT table_name
FROM   user_tables;
tname      VARCHAR2(200);
count_stmt VARCHAR2(5000);
count1     NUMBER;
count2     NUMBER;
BEGIN
OPEN c1;

FETCH c1 INTO tname;

WHILE c1%found LOOP
count_stmt := 'select count(1) from '
|| tname;

EXECUTE IMMEDIATE count_stmt INTO count1;

count_stmt := tname;

dbms_output.Put(count_stmt);

count2 := 30 - Length(tname);

WHILE count2 > 0 LOOP
dbms_output.Put(' ');

count2 := count2 - 1;
END LOOP;

dbms_output.Put_line(count1);

FETCH c1 INTO tname;
END LOOP;
END;

set serveroutput on to see the result

Solution 2

I was struggling to find some way to run dynamic sql query inside the select phrase something like

select table_name, (select count(*) from var_table_name) from user_tables

where var_table_name is variable that should hold value of table_name for each row processed from user_tables

and I found this

SELECT table_name,
To_number (Extractvalue (Xmltype (
dbms_xmlgen.Getxml('select count(*) c from '
||table_name)),
'/ROWSET/ROW/C')) AS COUNT
FROM   user_tables; 

dbms_xmlgen.getxml : Converts the results from the SQL query string to XML format, and returns the XML as a temporary CLOB
extractvalue  : gets the child value from the node.XML result from oracle is in the form

Oracle "create table as .. select " to add new column

I came across this requirement when one of my friend wanted to create new table from existing table but with  new columns (few extra column for future use) added to it.
He doesn't wanted to write 'Alter' script


Requirement:
Add new column 'comments varchar(30)'  with default  value as null to new_table
while creating copy of existing table old_table

Solution:
create table new_table as select old_column1 as new_column1 ,old_column2 as new_column2, cast('' as varchar2(30)) as comments from old_table;

Reason:

? Why we need to perform cast of null here
If  I don't perform cast here and add new column as
, '' as new_column1
Oracle gives error like 'column cannot be of zero length...' because it creates datatype of that column based on the value provided

Like if I give new column as
'null' as comments  it considers datatype as char(4) for comments column and successfully creates new column

So, when I try to give
'' as comments   it assumes we are trying to give datatype as char(0), which is not acceptable to Oracle

So we need to cast value here as varchar2 here.

Oracle Pivot Query using max decode, Row to Column Conversion

Requirement  1:

Consider scenario where we have table with following data. Here an id can belong to number of groups(grp)

[sourcecode language="sql"]
create table test1(id number,grp varchar2(1));
[/sourcecode]

id     grp
----------------
1      A
1      B
2      B
2      C
3      C

Required Output:
Distinct groups are displayed as column and a symbol x indicates that corresponding id is mapped to that group.

id  A   B   C
-------------------
1   x   x
2        x     x
3   x

There are fixed number of groups {A B C}, i.e cardinality of column grp is known and also all distinct values.

Solution

We will use the max(decode(..)) technique. This technique is used to convert rows to column and  column to rows.

First we perform decode operation to convert the rows value in to column.
We need to know number of distinct values and also their name in this case. For each distinct group, we write a decode as follows

[sourcecode language="sql"]
<pre>SELECT id,
Decode(grp, 'A', 'x',
NULL) a,
Decode(grp, 'B', 'x',
NULL) b,
Decode(grp, 'C', 'x',
NULL) c
FROM test1
ORDER BY id
[/sourcecode]

Result:

id  A   B   C
----------------
1  x
1        x
2        x
2               x
3        x

Output will have same number of rows with x  indicating mapping with corresponding id value.

Now we need to group rows by id, since we have distinct values for {id,group} only one x will be appearing for particular combination of id and grp in above result.

[sourcecode language="sql"]
SELECT id,
MAX(Decode(grp, 'A', 'x',
NULL)) a,
MAX(Decode(grp, 'B', 'x',
NULL)) b,
MAX(Decode(grp, 'C', 'x',
NULL)) c
FROM test1
GROUP BY id
ORDER BY id
[/sourcecode]

Result:

id  A   B   C
---------------
1   x   x
2        x    x
3        x

Requirement 2 : Consider same table and data but now required result is

Required Output:

id   grp1  grp2    grp3
--------------------------
1    A       B
2    B       C
3    C

that is we know distinct number of groups(we don't care about values or don't know values). So, all the groups to which id is mapped we need to show them in columns

Solution:

We will use the same max(decode(..)) technique but with slight modification.First for each id we will give a unique value to each grp value using analytic function row_number()

[sourcecode language="sql"]
SELECT id,
grp,
Row_number() over (PARTITION BY id ORDER BY grp) rn
FROM test1
[/sourcecode]

Result

ID GRP RN
----------------------
1    A    1
1    B   2
2    B   1
2    C   2
3    C   1

Now we use decode on rn field

[sourcecode language="sql"]SELECT id,
Decode(rn, 1, grp,
NULL) grp1,
Decode(rn, 2, grp,
NULL) grp2,
Decode(rn, 3, grp,
NULL) grp3
FROM (SELECT id,
grp,
Row_number() over (PARTITION BY id ORDER BY grp) rn
FROM test1)
ORDER BY id

[/sourcecode]

ID GRP1 GRP2 GRP3
--------------------
1     A        -          -
1     -         B         -
2     B         -          -
2     -          C        -
3     C         -          -

[sourcecode language="sql"]
SELECT id,
MAX(Decode(rn, 1, grp,
NULL)) grp1,
MAX(Decode(rn, 2, grp,
NULL)) grp2,
MAX(Decode(rn, 3, grp,
NULL)) grp3
FROM (SELECT id,
grp,
Row_number() over (PARTITION BY id ORDER BY grp) rn
FROM test1)
GROUP BY id
ORDER BY id
[/sourcecode]

Output:

ID GRP1 GRP2 GRP3
1      A      B       -
2       B      C       -
3       C       -       -