Home » SQL & PL/SQL » SQL & PL/SQL » Achieving Report Format in PL/SQL or SQL (Oracle 10g, Windows)
| Achieving Report Format in PL/SQL or SQL [message #627502] |
Wed, 12 November 2014 07:56  |
 |
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
|
Junior Member |
|
|
Hi All,
I have scenario where i need to achieve the reporting format in PL/SQL or SQL Logic but not through Report Builder.
Below is the sample data and the table structure.
CREATE TABLE employee1
(
ID NUMBER(4),
NAME VARCHAR2(100 BYTE),
salary NUMBER(4),
deptno NUMBER(4)
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (1, 'X', 1000, 10
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (2, 'Y', 2000, 20
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (3, 'Z', 5000, 10
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (4, 'A', 8000, 30
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (5, 'G', 4500, 20
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (6, 'F', 5600, 30
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (7, 'R', 4800, 10
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (8, 'E', 6000, 10
);
INSERT INTO employee1
(ID, NAME, salary, deptno
)
VALUES (9, 'L', 6200, 30
);
COMMIT ;
Actual Table Values
ID NAME SALARY DEPTNO
1 X 1000 10
2 Y 2000 20
3 Z 5000 10
4 A 8000 30
5 G 4500 20
6 F 5600 30
7 R 4800 10
8 E 6000 10
9 L 6200 30
My requirement is to achieve the below result but not in Report builder.
Result
Dept No Name Salary
10 X 1000
Z 5000
R 4800
E 6000
Total (10) 16800
20 Y 2000
G 4500
Total (20) 6500
30 A 8000
F 5600
L 6200
Total (30) 19800
This is just a sample data. There might be n number of departments.
I thought of using ROLLUP function but that will give the Dept no for each row and it will not display the "Total" word after each department.
Can you please help me out in achieving this by SQL or PL/SQL Logic.
[EDITED by LF: applied [code] tags]
[Updated on: Thu, 13 November 2014 00:20] by Moderator Report message to a moderator |
|
|
|
| Re: Achieving Report Format in PL/SQL or SQL [message #627503 is a reply to message #627502] |
Wed, 12 November 2014 08:00  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Before proceeding with any assistance, please tell us why you ignore to follow the forum guidelines, that too when you are part of the forum for more than 2 years.
And how about showing us first what you tried so far? Else it doesn't seem seeking help, rather seems asking someone to do your work.
[Updated on: Wed, 12 November 2014 08:05] Report message to a moderator |
|
|
|
| Re: Achieving Report Format in PL/SQL or SQL [message #627504 is a reply to message #627503] |
Wed, 12 November 2014 08:04 |
|
kumar0828
Messages: 22
Registered: August 2012
Location: Bengaluru
|
Junior Member |
|
|
Sorry to say lalit. I was not using forum for almost a year. SO didn't knew about guidelines.
Will make sure that is followed in my further posts.
This i can build with report builder.
But the person is asking to build in SQL or PL/SQL which i'm not able to do.
So seeking all you guys help.
[Updated on: Wed, 12 November 2014 08:06] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Achieving Report Format in PL/SQL or SQL [message #627520 is a reply to message #627502] |
Wed, 12 November 2014 09:45 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
For fun to get the exact expected result just in SQL (for any client program):
SQL> select decode(grouping(name),
2 0, decode(lag(deptno) over (order by deptno), deptno, '', to_char(deptno)),
3 'Total ('||deptno||')') "Dept No",
4 decode(grouping(name), 0, name) "Name",
5 sum(salary) "Salary"
6 from employee1
7 group by rollup(deptno, name)
8 having grouping(deptno) = 0
9 order by deptno, decode(grouping(name), 0, name, chr(255))
10 /
Dept No Name Salary
---------- ---------- ----------
10 E 6000
R 4800
X 1000
Z 5000
Total (10) 16800
20 G 4500
Y 2000
Total (20) 6500
30 A 8000
F 5600
L 6200
Total (30) 19800
|
|
|
|
|
|
| Re: Achieving Report Format in PL/SQL or SQL [message #627527 is a reply to message #627523] |
Wed, 12 November 2014 10:47 |
|
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Actually you don't need to test the grouping for the second column, when name is grouped then it is null which is what the expression I wrote does:
SQL> select decode(grouping(name),
2 0, decode(lag(deptno) over (order by deptno), deptno, '', to_char(deptno))
3 'Total ('||deptno||')') "Dept No",
4 name "Name",
5 sum(salary) "Salary"
6 from employee1
7 group by rollup(deptno, name)
8 having grouping(deptno) = 0
9 order by deptno, decode(grouping(name), 0, name, chr(255))
10 /
Dept No Name Salary
---------- ---------- ----------
10 E 6000
R 4800
X 1000
Z 5000
Total (10) 16800
20 G 4500
Y 2000
Total (20) 6500
30 A 8000
F 5600
L 6200
Total (30) 19800
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 19:22:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
