Forum Stats

  • 3,872,464 Users
  • 2,266,426 Discussions
  • 7,911,213 Comments

Discussions

How to Pivot Rows Into Columns

User_6Q8J8
User_6Q8J8 Member Posts: 38 Blue Ribbon
edited Sep 3, 2014 9:04AM in SQL & PL/SQL

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

Tagged:

Answers

  • Moazzam
    Moazzam Member Posts: 1,356 Silver Trophy

    You may use String Aggregation as discussed

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,722 Red Diamond
    edited Sep 3, 2014 9:04AM

    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:

This discussion has been closed.