Home » SQL & PL/SQL » SQL & PL/SQL » who can create this output?
who can create this output? [message #621166] Sun, 10 August 2014 22:54 Go to next message
hahaie
Messages: 128
Registered: May 2014
Senior Member
hello,
data is:
SELECT 'A' NAME_,10 OLD_YEAR,'M'SEX,50 WEIGHT FROM DUAL
UNION 
SELECT 'B' NAME_,14 OLD_YEAR,'F'SEX,60 WEIGHT FROM DUAL
UNION 
SELECT 'C' NAME_,12 OLD_YEAR,'F'SEX,57 WEIGHT FROM DUAL
UNION 
SELECT 'D' NAME_,11 OLD_YEAR,'M'SEX,54 WEIGHT FROM DUAL
UNION 
SELECT 'E' NAME_,5 OLD_YEAR,'M'SEX,45 WEIGHT FROM DUAL
UNION 
SELECT 'F' NAME_,9 OLD_YEAR,'F'SEX,40 WEIGHT FROM DUAL
UNION 
SELECT 'G' NAME_,8 OLD_YEAR,'F'SEX,47 WEIGHT FROM DUAL
UNION 
SELECT 'H' NAME_,14 OLD_YEAR,'M'SEX,40 WEIGHT FROM DUAL
UNION 
SELECT 'I' NAME_,17 OLD_YEAR,'F'SEX,38 WEIGHT FROM DUAL
UNION 
SELECT 'J' NAME_,5 OLD_YEAR,'M'SEX,55 WEIGHT FROM DUAL
UNION 
SELECT 'K' NAME_,12 OLD_YEAR,'M'SEX,65 WEIGHT FROM DUAL

Assuming the above data,How can create output in file attachment(with function,pipeline,select * from table...)?
  • Attachment: output.JPG
    (Size: 22.27KB, Downloaded 63 times)
Re: who can create this output? [message #621167 is a reply to message #621166] Sun, 10 August 2014 23:54 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2449
Registered: May 2013
Location: World Wide on the Web
Senior Member
hahaie wrote on Mon, 11 August 2014 09:24
How can create output in file attachment(with function,pipeline,select * from table...)?


And what did you try so far? SHOW using copy&paste.
Re: who can create this output? [message #621168 is a reply to message #621166] Mon, 11 August 2014 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL returns text lines not image, so please post in text and inline the result you want.

Note: Many of us can't or don't want to download files.

Re: who can create this output? [message #621174 is a reply to message #621166] Mon, 11 August 2014 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col old_years format a12 heading "      Weight|Old_year"
SQL> col sex       format a03 heading "|Sex"
SQL> col r1                   heading "30-40|"
SQL> col r2                   heading "40-50|"
SQL> col r3                   heading "50-60|"
SQL> break on old_years skip 1
SQL> with
  2    data as (
  3      SELECT 'A' NAME_,10 OLD_YEAR,'M'SEX,50 WEIGHT FROM DUAL
  4      UNION all
  5      SELECT 'B' NAME_,14 OLD_YEAR,'F'SEX,60 WEIGHT FROM DUAL
  6      UNION all
  7      SELECT 'C' NAME_,12 OLD_YEAR,'F'SEX,57 WEIGHT FROM DUAL
  8      UNION all
  9      SELECT 'D' NAME_,11 OLD_YEAR,'M'SEX,54 WEIGHT FROM DUAL
 10      UNION all
 11      SELECT 'E' NAME_,5 OLD_YEAR,'M'SEX,45 WEIGHT FROM DUAL
 12      UNION all
 13      SELECT 'F' NAME_,9 OLD_YEAR,'F'SEX,40 WEIGHT FROM DUAL
 14      UNION all
 15      SELECT 'G' NAME_,8 OLD_YEAR,'F'SEX,47 WEIGHT FROM DUAL
 16      UNION all
 17      SELECT 'H' NAME_,14 OLD_YEAR,'M'SEX,40 WEIGHT FROM DUAL
 18      UNION all
 19      SELECT 'I' NAME_,17 OLD_YEAR,'F'SEX,38 WEIGHT FROM DUAL
 20      UNION all
 21      SELECT 'J' NAME_,5 OLD_YEAR,'M'SEX,55 WEIGHT FROM DUAL
 22      UNION all
 23      SELECT 'K' NAME_,12 OLD_YEAR,'M'SEX,65 WEIGHT FROM DUAL
 24    ),
 25    ref as (
 26      select grp, sex
 27      from (select distinct trunc((old_year-1)/5) grp from data),
 28           (select distinct sex from data)
 29    ),
 30    counts as (
 31      select trunc((old_year-1)/5) grp, sex,
 32             nullif(count(case when weight between 30 and 40 then 1 end),0) r1,
 33             nullif(count(case when weight between 40 and 50 then 1 end),0) r2,
 34             nullif(count(case when weight between 50 and 60 then 1 end),0) r3
 35      from data
 36      group by trunc((old_year-1)/5), sex
 37    )
 38  select (1+5*r.grp)||'-'||(5+5*r.grp) old_years, r.sex, c.r1, c.r2, c.r3
 39  from ref r left outer join counts c on c.grp = r.grp and c.sex = r.sex
 40  order by r.grp, r.sex desc
 41  /
      Weight          30-40      40-50      50-60
Old_year     Sex
------------ --- ---------- ---------- ----------
1-5          M                       1          1
             F

6-10         M                       1          1
             F            1          2

11-15        M            1          1          1
             F                                  2

16-20        M
             F            1

[Updated on: Mon, 11 August 2014 03:01]

Report message to a moderator

Re: who can create this output? [message #621727 is a reply to message #621174] Sun, 17 August 2014 01:49 Go to previous messageGo to next message
hahaie
Messages: 128
Registered: May 2014
Senior Member
Marvellous.
Congratulations on the power of God!
but why trunc((old_year-1)/5)?

[Updated on: Sun, 17 August 2014 01:54]

Report message to a moderator

Re: who can create this output? [message #621729 is a reply to message #621727] Sun, 17 August 2014 02:23 Go to previous messageGo to next message
hahaie
Messages: 128
Registered: May 2014
Senior Member
ok,I've
but nullif(nvl)Does not work!
Re: who can create this output? [message #621730 is a reply to message #621727] Sun, 17 August 2014 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because you want each group (old_years column) contains 5 years and this formula gives this.

Re: who can create this output? [message #621731 is a reply to message #621729] Sun, 17 August 2014 02:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

hahaie wrote on Sun, 17 August 2014 09:23
ok,I've
but nullif(nvl)Does not work!


"Does not work" is not an Oracle error message.
Copy and paste what you did and got as I did it.

Before, Please read How to use [code] tags and make your code easier to read.

Re: who can create this output? [message #621733 is a reply to message #621731] Sun, 17 August 2014 04:14 Go to previous messageGo to next message
hahaie
Messages: 128
Registered: May 2014
Senior Member
No, not an error.
Only when the output is null, zero is not returned.
Re: who can create this output? [message #621734 is a reply to message #621733] Sun, 17 August 2014 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 17 August 2014 09:27

Copy and paste what you did and got as I did it.

Before, Please read How to use [code] tags and make your code easier to read.


Why do you refuse to do so?


Re: who can create this output? [message #621772 is a reply to message #621734] Mon, 18 August 2014 03:32 Go to previous messageGo to next message
hahaie
Messages: 128
Registered: May 2014
Senior Member
Because I've used your code exactly.
Which is at the top.
Re: who can create this output? [message #621774 is a reply to message #621772] Mon, 18 August 2014 03:34 Go to previous message
Lalit Kumar B
Messages: 2449
Registered: May 2013
Location: World Wide on the Web
Senior Member
hahaie wrote on Mon, 18 August 2014 14:02
Because I've used your code exactly.
Which is at the top.


Then you should get the output same as Michel's code exactly.
Which is at the top.
Previous Topic: What we should use for better function or procedure
Next Topic: Hierarchy output
Goto Forum:
  


Current Time: Sat Oct 25 13:32:06 CDT 2014

Total time taken to generate the page: 0.11823 seconds