?
Solved

SQL query / procedure

Posted on 2011-03-23
4
Medium Priority
?
424 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
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month17 days, 7 hours left to enroll

830 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