Similar Query in Oracle to declare a variable table.

[Vasuki Masilamani]

Hi,

I have a SQL query like this.

DECLARE @months table(Month int identity (1,1))
DECLARE @i int set @i = 0
WHILE(@i<12)
BEGIN
INSERT @months default values
SET @i = @i + 1
END


Can anyone help me with the Same Query in Oracle. I am new to Oracle.

Thanks,
Vasuki

[Vasuki Masilamani]

Hi,
I need a query which would convert Columns into Rows without causing any damages to the original data. I am not supposed to solve this by creating temporary tables and later dropping it.

I have a table which is in the below format:

Name EmployeeID Year JanExp FebExp MarExp JanHrs FebHrs MarHrs

A 129178 2005 10 20 30 15 25 35
B 129213 2004 40 50 60 45 55 65
C 129038 2005 70 80 90 75 85 95


I need a Output which has the following format:

Month Name EmployeeID Year Expenses Hours
Jan A 129178 2005 10 15
Jan B 129213 2005 40 45
Jan C 129038 2004 70 75
Feb A 129178 2005 20 25
Feb B 129213 2005 50 55
Feb C 129038 2004 80 85
Mar A 129178 2005 30 35
Mar B 129213 2005 60 65
Mar C 129038 2004 90 95

For this requirement I got a solution query in SQL. Here it is

declare @months table(Month int identity (1,1))
declare @i int set @i = 0
while(@i<3)
begin
insert @months default values
set @i = @i + 1
end

select Period = M.Month, t1.Name, t1.EmployeeID, t1.StartYear,
Expense = case
when M.Month = 1 then JanExp
when M.Month = 2 then FebExp
when M.Month = 3 then MarExp
end,
Hours = case
when M.Month = 1 then JanHrs
when M.Month = 2 then FebHrs
when M.Month = 3 then MarHrs
end
from #test1 t1, @months M
order by M.Month

I need to do this in Oracle SQL*Plus. Can anyone help me how to declare a variable table in Oracle?

Thanks,
Vasuki

[suvam]

If u don't go for creating a Temp table then try this SQL to directly fetch the resulset in ur format -------->
Select Name, EmployeeID, Year ,
Substr(a.Column _name,1,3) Month,
Sum(Decode(a.Co lumn_name ,'JANEXP',b.Jan Exp,'FEBEXP',b. FebExp,'MAREXP' ,b.MarExp,0)) Exp ,
Sum(Decode(a.Co lumn_name,'JANH RS',b.JanHrs,'F EBHRS',b.FebHrs ,'MARHRS',b.Mar Hrs,0)) Hours ,
From All_Tab_Columns a ,Employee b
Where a.Table_Name = 'EMPLOYEE'
and a.column_name in ('JANEXP','FEBE XP','MAREXP','J ANHRS','FEBHRS' ,'MARHRS')
Group By Substr(a.Column _name,1,3) ,Name, EmployeeID, Year
Order By to_date(Substr( a.Column_name,1 ,3),'Mon') ;