?
Solved

SQL query / procedure

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

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ā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

777 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