Home » SQL & PL/SQL » SQL & PL/SQL » How to display data in a required format by SQL Select Query? (Oracle Database 10g Enterprise Edition Release 10.1.0.2.0; Windows Xp.)
How to display data in a required format by SQL Select Query? [message #572456] Wed, 12 December 2012 03:35 Go to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Hi,

i like to display a Table data like the below format,

Output:

EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

00094 122 153 145 224 245 545 114 544 444 111 555 222
00095 233 123 145 224 205 545 114 444 444 111 555 222
00096 163 123 145 224 215 545 114 551 444 111 555 222
00097 163 0 145 224 215 545 114 551 444 111 555 222
conditions:

where condition:
where year = 2007


Table Structure:

create table HR_PAYSLIP
(
  EMP_NO         VARCHAR2(6) not null,
  YEAR           NUMBER(4) not null,
  MONTH          NUMBER(2) not null,  
  BASIC_PAY      NUMBER(9,2), 
)



Insert Command;


INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY)
VALUES(00046, 2007, 1, 2314);


Pls Note:
The above table data i have mentioned is an example with employee numbers and the basic_pay for all months
in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007,
and if the basic salary is zero for a month then it should be displayed as zero for a particular month

So how to write a Query for that?

[Updated on: Wed, 12 December 2012 03:46] by Moderator

Report message to a moderator

Re: How to display data in a required format by SQL Select Query? [message #572457 is a reply to message #572456] Wed, 12 December 2012 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a basic "pivot" query but we can't show as we have not the table and data for this, so search for this word.

Here an example with the standard SCOTT.EMP table:

SQL> select extract(year from hiredate) year,
  2         count(decode(deptno,10,1)) dept10,
  3         count(decode(deptno,20,1)) dept20,
  4         count(decode(deptno,30,1)) dept30
  5  from emp
  6  group by extract(year from hiredate)
  7  order by 1
  8  /
      YEAR     DEPT10     DEPT20     DEPT30
---------- ---------- ---------- ----------
      1980          0          1          0
      1981          2          2          6
      1982          1          0          0
      1987          0          2          0


Once more:

With any SQL or PL/SQL question, please, Post a working AND COMPLETE Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
In your example what is 122 153 145 224 245 545 114 544 444 111 555 222?

Regards
Michel
Re: How to display data in a required format by SQL Select Query? [message #572460 is a reply to message #572457] Wed, 12 December 2012 04:03 Go to previous messageGo to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Quote:
In your example what is 122 153 145 224 245 545 114 544 444 111 555 222?


that is example figures for basic_pay for particular month,
when i copy it from the sql its not viewing correcly after posting. so it colapses and you cannot able to
recognize it.

Jan Feb Mar apr may jun jul aug sep oct nov dec
122 153 145 224 245 545 114 544 444 111 555 222 (Basic Pay figures)

i need the basic pay amounts or figures below the month columns but every months alias for basic pay
is shown as jan, feb, mar upto dec.

Re: How to display data in a required format by SQL Select Query? [message #572463 is a reply to message #572460] Wed, 12 December 2012 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And we need you provide us a COMPLETE test case, if you want we show you with your table and data.
Anyway, you have the solution with my examples, just work on it.

Regards
Michel
Re: How to display data in a required format by SQL Select Query? [message #572464 is a reply to message #572457] Wed, 12 December 2012 04:26 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
Hi Stalin,

Try this query
------------------------------------------------------------------------------------------------------------------------------------
SELECT   emp_no, SUM (jan) jan, SUM (feb) feb,SUM (mar) mar,SUM (apr) apr,
SUM (may) may,SUM (jun) jun,SUM (jul) jul,SUM (aug) aug,SUM (sep) sep,
SUM (oct) oct,
SUM (nov) nov,SUM (dec) dec
    FROM (SELECT   emp_no, DECODE (MONTH, 1, SUM (basic_pay)) jan,
                   DECODE (MONTH, 2, SUM (basic_pay)) feb,
                   DECODE (MONTH, 3, SUM (basic_pay)) Mar,
                   DECODE (MONTH, 4, SUM (basic_pay)) Apr,
                   DECODE (MONTH, 5, SUM (basic_pay)) May,
                   DECODE (MONTH, 6, SUM (basic_pay)) Jun,
                   DECODE (MONTH, 7, SUM (basic_pay)) Jul,
                   DECODE (MONTH, 8, SUM (basic_pay)) Aug,
                   DECODE (MONTH, 9, SUM (basic_pay)) Sep,
                   DECODE (MONTH, 10, SUM (basic_pay)) Oct,
                   DECODE (MONTH, 11, SUM (basic_pay)) Nov,
                   DECODE (MONTH, 12, SUM (basic_pay)) Dec
              FROM hr_payslip
          GROUP BY emp_no, MONTH)
GROUP BY emp_no  



Veera

[Updated on: Wed, 12 December 2012 04:29] by Moderator

Report message to a moderator

Re: How to display data in a required format by SQL Select Query? [message #572467 is a reply to message #572464] Wed, 12 December 2012 04:39 Go to previous messageGo to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
hello mvmkandan,

this is what i need and thanks for your valuable Reply....
Re: How to display data in a required format by SQL Select Query? [message #572469 is a reply to message #572467] Wed, 12 December 2012 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks everyone that tries to help you otherwise you will have help only from the one you thanked.

Regards
Michel
Re: How to display data in a required format by SQL Select Query? [message #572471 is a reply to message #572467] Wed, 12 December 2012 05:01 Go to previous messageGo to next message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Quote:
And we need you provide us a COMPLETE test case, if you want we show you with your table and data.
Anyway, you have the solution with my examples, just work on it.

Regards
Michel


we cannot provide the data because of some official purpose we can't give that
thats why i mentioned with insert command,and create table command where you can create, insert and show
write the select query.
Quote:

here with im mentioning the query which mvmkandan replied i took this data
from PLSQL DEVELOPER tool(same like toad)

Quote:

EMP_NO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
1 94 18660 18660 18660 18660 18660 18660 18660 19260 19260 19260 19260 19260
2 130 16330 16330 16330 16330 16330 16330 16330 16930 16930 16930 16930 16930
3 133 14450 14450 14450 14450 14450 14450 14450 15050 15050 15050 15050 15050
4 147 14750 14750 14750 14750 14750 14750 14750 15350 15350 15350 15350 15350
5 156 16260 16260 16260 16260 16260 16260 16260
6 187 31810
7 193 19360 19360 19360 19360 19360 19360 19360 19960 19960 19960 19960 19960
8 236 29260 29260 29260 29260 29260 29260 29260 29860 29860 29860 29860 29860
9 237 28360 28360 28360 31200 31200 31200 31200 31800 31800 31800 31800 31800
10 238 24470 24470 24470 24470 24470 24470 24470 25070 25070 25070 25070 25070
11 250 18670 18670 18670 18670 18670 18670 18670 19270 19270
12 253 35670 35670 35670 35670 35670 35670 35670 35670 35670 35670 35670 35670
13 263 18360 18360 18360 18360 18360 18360 18360 18960 18960 18960 18960 18960


Anyhow thank U all for your replies.
Re: How to display data in a required format by SQL Select Query? [message #572482 is a reply to message #572471] Wed, 12 December 2012 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we cannot provide the data because of some official purpose we can't give that


A test case is NOT actual data you can give what you want but given the table you showed us I don't what is confidential, can you explain?

Quote:
thats why i mentioned with insert command,and create table command where you can create, insert and show write the select query.


Too few data to test if a query is correct or not.
There should be at least 1 or 2 rows per month for 2 or more empno.

Regards
Michel
Re: How to display data in a required format by SQL Select Query? [message #572525 is a reply to message #572482] Wed, 12 December 2012 23:00 Go to previous message
stalin4d
Messages: 214
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
its ok now i got the idea and answer. thanks a lot for your replies.
Previous Topic: How to create a procedure to read data from database and export it into .csv format
Next Topic: Unable to Drop a Procedure.
Goto Forum:
  


Current Time: Sun Sep 21 15:34:21 CDT 2014

Total time taken to generate the page: 0.09070 seconds