• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

sql

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


I have a table log_tbl with column EMP_RESULT




I want to display the output below:




Can anyone help me to query the above table

thanks

 
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Kal,

how much do you know about sql?

As a hint: try combining substr() and count(*)/group by:

substr

count(*)
Group Functions



John
 
phani ravinutala
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here is my query




I got the below output:


But I want the below output:





Can any one help me to modify my above query?

Thanks
 
John Bengler
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Kal,

you can try this:



If the length of the part you want to group by varies you can e.g.try to use INSTR to find the positionof the "ID:".


John
 
phani ravinutala
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your reply.

I have one more question suppose, if i have two tables deptlog_tbl and log_tbl log_tbl with data like below


deptlog_tbl




How would i query to join the two tables to get the below output?




thanks





 
John Bengler
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Kal,

first a short querstion: Is this a kind of legacy db your're working with?

If not:

It's a little strange to have a table with only one column where the data is structured by "tags".
For your problem it would be much easier if your log_tbl would look like this:

DEPT_CODE NUMBER(4) NOT NULL
MSG VARCHAR2(40) NOT NULL
TRACKING_ID VARCHAR2(200)

where DEPT_CODE and MSG are the foreign key to deptlog_tbl.


But let's work with the structure you described in your post.


First of all I do not think you're really joining the two tables, at least I can't see anything in your result which you can't select directly from table log_tbl.

Without joining the problem looks pretty much like the one from the beginning of this thread. Maybe a problem could be that some strings are shorter than the substring you are using.


You should have a look at the Oracle string functions:

Oracle String Functions

The result maybe something like this:



I hope I did understand what you want to do...


John
reply
    Bookmark Topic Watch Topic
  • New Topic