OraFAQ Forum: SQL & PL/SQL » column names are row values from another table.

Home » SQL & PL/SQL » SQL & PL/SQL » column names are row values from another table. (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

column names are row values from another table. [message #628164]

Thu, 20 November 2014 14:07

shree_z
Messages: 75
Registered: February 2008

Member

I have a calendar table with dates and attributes

In a work_hours table there are columns week1, week2, week3... week52. these columns has data - number of hours worked in that corresponding weeks.

week1   week2    week3...
  4       3       5

Now I want to rename week1 with the actual week_start_date from the calendar table for the date corresponding to week_number = 1

calendar table

week_number  week_start_date
      1            1/1/2014
      2            1/8/2014
      3            1/15/2014

I want the final table to be like this

1/1/2014   1/8/2014   1/15/2014..
  4           3         5

Can I use PIVOT? But I have no idea how to do this.
Thanks in advance

[Updated on: Thu, 20 November 2014 14:12]

Report message to a moderator

Re: column names are row values from another table. [message #628165 is a reply to message #628164]

Thu, 20 November 2014 14:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Horrible design & worse proposed change!
Column names should never include any actual application data like a specific DATE.
The root cause is that you have NOT normalized the data; either current or proposed
The normalized table should be like below:

CREATE TABLE emp_hours_worked 
  ( 
     employee_id  NUMBER, 
     date_worked  DATE, 
     week_of_year NUMBER, 
     hours_worked NUMBER 
  );

Report message to a moderator

Re: column names are row values from another table. [message #628166 is a reply to message #628165]

Thu, 20 November 2014 14:26

shree_z
Messages: 75
Registered: February 2008

Member

The table is meant for report use alone, so as to avoid steps in the report layer. Is there a way to attain this?

Report message to a moderator

Re: column names are row values from another table. [message #628168 is a reply to message #628166]

Thu, 20 November 2014 14:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The first rule of holes is below.
The FIRST thing you should do when you find yourself in a hole is to STOP DIGGING!
Your proposed change makes the problem worse, not better.

>The table is meant for report use alone, so as to avoid steps in the report layer.
until the reporting requirements change (again)

I refuse to aid and abet unprofessional behavior.

Report message to a moderator

Re: column names are row values from another table. [message #628169 is a reply to message #628168]

Thu, 20 November 2014 14:48

shree_z
Messages: 75
Registered: February 2008

Member

OK.. so here if I want to create a better work hours table , how should I transpose week1 to week 52 columns data to rows. I would like to use the data from the old table.

Report message to a moderator

Re: column names are row values from another table. [message #628170 is a reply to message #628169]

Thu, 20 November 2014 14:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

1) select 1 row
2) do 52 INSERT statements
3) goto #1 until no more rows

Report message to a moderator

Re: column names are row values from another table. [message #628172 is a reply to message #628164]

Thu, 20 November 2014 15:12

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If week 1 starts on January 1st, you don't need any table:
Week N of the year starts on "to_date('01/01/2014','DD/MM/YYYY) + 7*(N-1)"
And date dt is in the week "trunc((dt-trunc(dt,'YEAR'))/7)+1"

Report message to a moderator