[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL query / procedure

Posted on 2011-03-23
4
Medium Priority
?
423 Views
Last Modified: 2013-12-07
Hi,

Have a table called tbl1 like :

UPDATEDATETIME       dBDate               HFCol      HVCol
3/10/2011 13:06:53       6/10/2011      10369.8      0.1979
3/10/2011 13:06:53       3/11/2011      10440      0.2059
3/10/2011 13:07:03       6/10/2011      10369.8      0.1976
3/10/2011 13:07:03       3/11/2011      10440      0.2036
3/10/2011 13:07:13       6/10/2011      10369.8      0.1975
3/10/2011 13:07:13       3/11/2011      10440      0.2038

and struggling to get sql/sotre proc to produce following results:

UPDATEDATETIME      HFCol_JUN11      HFCol_MAR11       HVCol_JUN11       HVCol_MAR11
3/10/2011 13:06:53      10369.8             10440               0.1979               0.2059
3/10/2011 13:07:03      10369.8             10440               0.1976               0.2036
3/10/2011 13:07:13      10369.8              10440               0.1975               0.2038

[As you can see new columns in result are old columns and month and year from dbDate is appended. For eg. HFCol for dbDate 03/11/2011 becomes HFCOL_MAR11 etc.]
                        
Thanks,

0
Comment
Question by:punit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35204637
it's a pivot table ... must it be fully dynamic, or are you happy with those 2x2 columns?
0
 

Author Comment

by:punit
ID: 35204643
hi angelll,
it should be dynamic.
One more note, as you can see what I'm trying to do is trying to put data for same UPDATEDATETIME  into one row.

Thanks
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 35204909
could the data be over 1 year? how to represent that?
also, could there be more than 1 row per "month" ?

let's assume for the moment that it was all from 1 year
select UPDATEDATETIME       
  , max(case when month(dBDate) = 1 then  HFCol else null end) HFCol_Jan
  , max(case when month(dBDate) = 1 then  HVCol else null end) HVCol_Jan
  , max(case when month(dBDate) = 2 then  HFCol else null end) HFCol_Feb
  , max(case when month(dBDate) = 2 then  HVCol else null end) HVCol_Feb
  --- etc for the other month
 from tbl1 
group by UPDATEDATETIME

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 35235074
See attached.
query.txt
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question