|
|
-
|
|
Let Us Learn Oracle - Part 25 of N [ UnPivot Function of Oracle 11g ]
Purpose: Use for transposing columns into rows
It is exactly the same as Sql Server 2005 UnPivot.
Example
SQL > Select
ItemNo
,ItemName
,Amount
From tblUnPivotExample
UnPivot
(
Amount
For ItemName......
|
|
-
|
|
WITH ModCte as
(
SELECT
RowsValues
,Col
,DECODE(MOD(RowsValues,Col),0,0,1) AS ModVal
FROM
(
SELECT RowsValues,Col
FROM tblCols
CROSS JOIN tblRows
)
)
SELECT RowsValues,
CASE WHEN [1]= 0 THEN 'X' ELSE '' END [1]
,CASE WH......
|
|
-
|
|
Select DeptName,ListAgg(EmpName,',') Within Group (Order By EmpName) Concat_Employees
From tblDept
Join tblEmployee
Using(DeptId)
Group By DeptName;
DeptName Concat_Employees
-------- ---------------
Accounts Shashi Dayal
Finance Sumanta Man......
|
|
-
|
|
It can be a variable......
|
|
-
|
|
Let Us Learn Oracle - Part 21 of N [ RowNum in Oracle ]
Source table: tblEmployee
It is a pseudo column that generates sequences in the result set starting from 1 and is evaluated BEFORE the execution of ORDER BY clause and AFTER records are selected.
SQL> Select RowNum,EmpId,EmpName,Salary,D......
|
|
-
|
|
Let Us Learn Oracle - Part 20 of N [ WM_Concat Function in Oracle ]
Source table: tblDept/tblEmployee
Purpose:This function is use for string aggregation.It puts a comma between each value it concatenates. But the problem with this function is that it does not order the elements in the concatenat......
|
|
-
|
|
Let Us Learn Oracle - Part 19 of N [ Joins in Oracle ]
Source table: tblEmployee
Joins in Oracle are similar to Sql Server with some more thing in place. We will look into those shortly
Cross Join / Cartesian Join
Natural Join
Equi Join/ Inner Join
Using clause
Left Outer Join
Right Out......
|
|
-
|
|
Let Us Learn Oracle - Part 18 of N [ Date functions in Oracle ]
Source table: tblPlayers
Oracle has a rich set of Date functions about which we will see under
Get Current Date
For getting current date we can use Sysdate or Current_Date
SQL> Select SysDate,Current_Date From Dual;
SYSDATE......
|
|
-
|
|
Let Us Learn Oracle - Part 17 of N [ Set Operators in Oracle ]
Like Sql Server, Oracle has 4 Set Operators
Union
Union All
Intersect
Minus ( Except in Sql Server)
Let us create two tables Table A
Create table TableA(ID int);
Insert Into TableA(ID) Values(10);
Insert Into TableA......
|
|
-
|
|
Let Us Learn Oracle - Part 16 of N [ Greatest and Least function in Oracle ]
Source table: tblStudents
Purpose: Finds the Greatest and the Least number from a range of numbers
Example of Greatest function
SQL> Select Greatest(10,20,4,56,234,999,12,56,-45,23,77,123) GreatestNumber From Dual;......
|
|