Post reply

How to get data for whole month

  • January 6, 2010 at 3:53 am

    #137940

    Hi,

    I am storing transaction data for users vertically as I have shown below in table1.

    And now I need to get the data for whole month horizontally as I have shown below in table2.

    Please let me know how to write query for same ?

    Table1:-

    **user Date trans**

    a 1/1/2010 10

    a 1/1/2010 10

    b 1/1/2010 20

    b 1/1/2010 20

    a 2/1/2010 30

    b 2/1/2010 40

    b 4/1/2010 60

    Table2:-

    **user 1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010**

    a 20 30 0 60 0

    b 40 40 0 0 0

    I want data for whole month (till last day of the month) as shown above.

    Regards,

    harsha.

  • January 6, 2010 at 4:07 am

    #1099453

    A case of dynamic cross tab or pivot i guess. Read this excellent article.

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    ---------------------------------------------------------------------------------

  • January 6, 2010 at 4:07 am

    #1099454

    Please have a look at the two articles in my signature regarding CrossTab and DynamicCrossTab. I guess you can't use PIVOT since your column names and number of columns will be dynamic...

    If you have questions related to the article or how to modify it to meet your situation post back what you've tried and where you got stuck.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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