How to Pivot Rows Into Columns

User_6Q8J8

Regularhours   overtime   workhourdate

0                       0                  7/20/2014

8                       2                  7/21/2014

0                       4                  7/22/2014

0                       0                  7/23/2014

0                       0                  7/24/2014

8                       0                  7/25/2014

0                       0                  7/26/2014

Desired out put

                     7/20/2014      7/21/2014      7/22/2014     7/23/2014     7/24/2014     7/25/2014     7/26/2014 

Regular              0                       8                0                    0                        0                 8                      0

Overttime           0                       2                4                    0                       0                 0                       0

the workhourdate  can change based on the user selection of dates. It is always 7 days.  for example it could be between 7/13-7/20 or in this case the user want the report for 7/20-7/26

thank you

Moazzam

You may use String Aggregation as discussed

Frank Kulash

Hi,

For how to pivot, see the Forum FAQ:

In your case, you'll want to subtract workhourdate from the given start (or end) date, to get a number of days, between 0 and 6, say, and pivot on that number.

The column names have to be hard-coded into the query when it compiles.  If you want column names that depend on the data, or on a run-time parameter, then you need Dynamic SQL.  Your from end can help witht that.  For example, in SQL*Plus, you can use the COLUMN command to set substitution variables dynamically.  Is that an option for you?

Instead of dynamic column aliases, you can include the dynamic test in the result set, and display them as if they were column headings.  See

I hope this answers your question.

If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, the results you want from that data, and your best attempt at a query.

Explain, using specific examples, how you get those results from that data.

Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

See the forum FAQ: