Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Help me to build this query

    Hi friends...
    I event don't know the name of what I am trying to do to google it.. I'm using sql2008 and here's the problem: 3 tables must be come together to form the table I wrote below..
    SOURCE TABLES:

    Productstable
    ----------------
    beer
    keyboard
    chair


    SalesPointstable
    ------------------
    newyork
    berlin
    moscov
    zurich


    SalesPricesTable
    --------------------------------
    beer..........newyork..4..USD
    beer..........berlin...5..EURO
    beer..........moscov...6..ROUBLE
    beer..........zurich...7..FRANK
    keyboard......newyork..6..USD
    keyboard......berlin...7..EURO
    keyboard......moscov...8..ROUBLE
    keyboard......zurich...7..FRANK
    chair.........newyork..6..USD
    chair.........berlin...7..EURO
    chair.........moscov...8..ROUBLE
    chair.........zurich...6..FRANK

    I NEED THIS RESULT TABLE:

    ..........newyork....berlin...moscov....zurich
    beer........4.USD....5.EURO..6.ROUBLE...7.FRANK
    keyboard....6.USD....7.EURO..8.ROUBLE...7.FRANK
    chair.......6.USD....7.EURO..8.ROUBLE...6.FRANK


    what can be the query ? I could not do it with pivot commands nor I could not designed this query.

    any idea would be great. best regards.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many products do you have?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is just a pivot. It should really be handled by your user interface (Access, Crystal, Excel, .Net, etc) as it is inherently a display issue. But if you need to do it with SQL you can use the CASE statement, and there is an excellent example of how to do it in Books Online.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2009
    Posts
    2
    my friend,

    pivot didnt work because it can ony accept aggregate function. I also need money indormation (euro, usd, etc...) I did run pivot but could not append USD nor EURO to it. that makes 2 columns of pivot information. price and money information. also pivot did not accept string functions.

    product count, sales point count, and even money count (usd, eoru, rouble, etc) are not pre-defined.

    also I cannot solve it with case function because named are actually unknown and entered by customer to database. Even money names are not known. it can be usd, eoru or even japaneese yen. basically everything in this query must be obtained from tables dynamically.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by FriendOfGhost View Post
    my friend,

    pivot didnt work because it can ony accept aggregate function.
    My friend,

    What is MAX() or MIN() of a single value?
    A CASE based pivot will work fine. Give it a try, and post the code you come up with.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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