SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Transpose / Pivot / AddColumns

    Dear all,

    Anyone can help? Please....

    My question is that I want to create a table with UNKNOWN number of columns. It depends on how many 'months' the user entered. The table looks like:

    Item JAN FEB MAR APR ....... TOTAL
    --------------------------------------------------------------------
    Apple 45 43 22 44 325
    Orange 23 44 22 55 346
    Banana 19 15 32 23 232

    All the data are called from a table call 'master'. The column of months generate will depend how the user select. It could be from JAN to MARCH or from FEB to DEC. And the TOTAL would be calculated based on the number of months the user chose.

    I am thinking which method I should use ( Transpose/ Pivot / AddColumns ). However, NONE of the method I know... So, please help...

    Many thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, a table cannot have an unknown number of columns

    but you were probably thinking of a query, right?

    i can't think of any easy way to do this except as a crosstab query in microsoft access

    and even then, it's going to be a kludge to get the month names as column headers and not have them sorted alphabetically, i.e. apr aug dec feb jan jul jun mar may nov oct sep

    if you allowed always 12 columns in the result, you could do it a bit easier with some semi-complex sql, and it would work in other databases as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cuz' the original database like this:

    Code Item Quanity Date Price
    342 Apple 30 01/02/04 .49
    343 Apple 18 02/02/04 .67
    344 Apple 23 04/03/04 .56

    256 Orange 19 03/05/04 .24
    257 Orange 20 04/06/04 .36
    ....

    The user will select the range of the date ( from when to when ), then it will generate looking like the followings:

    Item JAN FEB MAR APR ....... TOTAL
    --------------------------------------------------------------------
    Apple 45 43 22 44 325
    Orange 23 44 22 55 346
    Banana 19 15 32 23 232

    The column of month will depend on the month entered by the user ( from which month to which month ). I am using ASP.NET in VB script. But I think that it's easier for me to work on the SQL side, right?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by maggiet
    But I think that it's easier for me to work on the SQL side, right?
    no, wrong, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any suggestion then?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, do it with asp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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