Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    65

    Unanswered: SQL help-Period names on columns.

    Hello to all. I need some help on SQL as Im new to it. I have the following two tables:
    AMOUNTS (a)
    Account_ID
    Period_name
    Amount

    ACCOUNTS (b)
    Acount_ID

    By using the following SQL query:
    Code:
    SELECT b.Account_ID , SUM(a.Amount), a.Period_name
    FROM AMOUNTS a, ACCOUNTS b
    WHERE a.Account_ID = b.Account_ID 
    GROUP by b.Account_ID,a.Period_name
    I get
    Account_ID SUM(Amount) Period_name
    XXXXX XXXXX AAAAA
    XXXXX XXXXX BBBBBB

    How can I get?
    Account_ID AAAAA BBBBB
    XXXXXX XXXXX XXXXX

    Thanks in advance for any kind help.
    Octavio

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    What are 'XXXXX', 'AAAAA' & 'BBBBB'? Can you tell us more clearly what it is you're trying to do?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jun 2007
    Posts
    65
    Thanks for answering. What i mean is, with the query i have, if there are two different periods i'll get 3 columns and two rows:
    AccountID SUM(Amount) PeriodName
    'XXXXX' '99999999,99' AAAAA
    'XXXXX' '88888888,99' BBBBB

    What i would like to have (if possible) is:

    AccountID AAAAA BBBBB
    'XXXXX' '99999999,99' '88888888,99'

    Basically, having the period names in the columns instead of in the rows.

    Thank you,
    Octavio

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    For me, it looks like some kind of pivotting. Here are some methods for achieving it: https://forums.oracle.com/message/9362005#9362005

    Just implement the one available for your Oracle version and fulfilling the exact requirements.

  5. #5
    Join Date
    Jun 2007
    Posts
    65
    Thank you for answering.
    In fact, i found PIVOT function, which seems to suit to my needs. However, i can't put it it to work. My database version Oracle Database 11g Enterprise Edition Release 11.2.0.3.0.
    I always get a "Missing keyword error..."

    My query:
    Code:
    SELECT *
    FROM
         (SELECT code_combination_id, period_name, nvl(sum(begin_balance_dr + period_net_dr - begin_balance_cr - period_net_cr),0)
          FROM gl_balances
         ) PIVOT (nvl(sum(gb.begin_balance_dr + gb.period_net_dr - gb.begin_balance_cr - gb.period_net_cr),0))

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    I can't see any error message. Post the SQL session, including your query & the result.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Yes, it is incorrect. I see two major mistakes here: missing alias in the inner query (so you cannot reference the computed column outside) and missing FOR clause.

    As you did not post a test case - CREATE TABLE statements for table structure and INSERT statements for sample data - and I have no GL_BALANCE table in my Oracle database, here is equivalent query for SCOTT.EMP - converting JOBs to columns aggregated by DEPTNO:
    Code:
    select *
    from ( select deptno, job, nvl(sal+comm,0) some_measure from emp )
    pivot ( sum(some_measure) 
      for job in ( 'CLERK', 'SALESMAN', 'ANALYST', 'MANAGER', 'PRESIDENT'  ) );

  8. #8
    Join Date
    Jun 2007
    Posts
    65
    Got it!

    Thanks a million!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •