Results 1 to 4 of 4

Thread: Using Decode

  1. #1
    Join Date
    Sep 2006
    Posts
    2

    Unanswered: Using Decode

    Hello,

    Is there a way to not have to enter all possible entries (values) taken from a table to have displayed.

    Here is what I mean....if i know there are just two or three entries I do the following.....

    select bmtele,sum(decode(bmitem,'STATION',bmchar,0))stati on,
    sum(decode(bmitem,'OVERHEAD',bmchar,0))overhead
    from bill
    where bmtele = '33333333333'
    group by bmtele;

    if there are unknown amount of entries, how would i code (meaning more than the two above)?

    thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    You could use the following SQL to create a 'crosstab' or 'pivot table':
    Code:
    select bmtele, bmitem, SUM(bmchar) bmchar
      from bill
     where bmtele = '33333333333'
     group by bmtele, bmitem;

    PS: Research (or search) for 'crosstab' or 'pivot table' (or 'rows to columns').

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    use a CASE statement instead.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think he means how to do a crosstab report when you dont know at compile time the number of columns to return.

    If that is the case you can do dynamic sql to select all the possible values and then construct your sql statement as a string which you later execute (say using java).

    The other possibility is to use stragg(see AskTom) to concatenate all the possible values together as a comma seperated list. But then you get them in just one column which may or may not be OK depending on what your looking for.

    For a really leading edge solution (or should I say bleeding edge )try this

    http://technology.amis.nl/blog/?p=1207

    Alan

Posting Permissions

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