Post reply

SQL statement question

  • September 18, 2005 at 9:57 am

    #168365

    Hi Gurus,

    Here is a simple question:

    I have a table with columns as

    StockId TransactionId Amount

    MSFT     1     500

    MSFT     1     2500

    MSFT     2    1000

    CSCO     1    2500

    CSCO      1 2500

    AMZN     1   4000

    AMZN     2   5000

    AMZN      2  4000

    I want to get a result set with rows like these(basically group by stock id, avg amount for each of the transactionids) in a single sql statement.

    StockId  AvgAmountforTransaction id1 AvgAmountforTransactionid2

    MSFT     1500   1000

    CSCO     2500

    AMZN    4000    4500

     

    How do I do it?

     

    Thanks in advance ! This is my first post - pls. let me know if this is not the right forum to ask about sql server sql issues.

  • September 18, 2005 at 12:17 pm

    #590821

    Are there always two transaction IDs for each StockID, or is the number variable (in which case, you want a variable number of columns)?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • September 19, 2005 at 8:34 am

    #590965

    Number is variable

  • September 19, 2005 at 8:39 am

    #590967

    This cannot be done in a single SQL statement.  If you definitely need it in this format, you'll need a chunk of code (examples of which appear elsewhere on this site - you're definitely not the first to want this).

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • September 19, 2005 at 9:27 am

    #590982

    It can be done.  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=220954 

    Please don't cross post.   

    I wasn't born stupid - I had to study.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply