Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2007
    Posts
    11

    Unanswered: query result layout

    Dear All

    I have a specific requirement by client. The company has is existence in 32 countries. so he wants the output in this form

    Division USA NL Turkey India Srilanka Japan China
    Division A 30 20 50 42 25 12 84
    Division A 30 20 50 42 25 12 84
    Division A 30 20 50 42 25 12 84

    The number indicate total sales figure based on countries and division. Is it possible to construct query in this format, where the column will be the name of countries and row indiacate the total sales figures.

    Thanks

    --
    Shailesh Patil

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Almost any reporting tool can do this. I'd suggest either Microsoft Reporting Services if you need a server based solution or Microsoft Access for a workstation based solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *shrug*
    Here's one long winded way.


    I'd run with Pats suggestion if possible - report writers are miles better at this sort of thing.
    Code:
    SELECT division
         , Sum(CASE WHEN country = 'USA' THEN sale_value END) AS usa
         , Sum(CASE WHEN country = 'NL' THEN sale_value END) AS nl
         , ...
    FROM   your_table
    GROUP
        BY division
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2007
    Posts
    17
    you may try this:

    select A.val,B.val,C.val
    from A
    left join B
    on 1=1
    left join C
    on 1=1

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Marydan, I'm afraid your query makes no sense to this situation!

    Since when were 3 tables involved, and why are you cross joining each of them?
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2007
    Posts
    11
    Dear All

    Thanks for your immediate reply.
    Actually this has to be implemented on dashboard. So, on particular item, if user clicks on, he will see the count per division per country. and clicking on specific section he gets the details of figure.
    I would like to create a query which displays all the country as header and corresponding rows should be the result of query. for eg.
    select country_id, country_vc from country_t should produce an output like
    USA NL Turkey India Srilanka Japan China
    AND underlying should be result as i mentioned in earlier post.

    It is possible to do it in php, but i am afraid, it will take long time to retrieve all this information.
    So, (no. of division * no. of countries), a query will be fired
    OR
    another solution will be using arrays, looping and then using conditional statements to print it.
    Just a thought, is it possible to do it in MSSQL itself?

    Kindly suggest me a better alternative to problem, which helps reduce the execution time.

    Many thanks

    --
    Shailesh Patil

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This should be done by your front end - the result you want is not relational. You should take any longer to retrieve this data - it is the formatting that changes, and this is a front end issue.

    Another option, if you insist on using SQL Server, is to use the PIVOT clause. Note that this option and George's option will both require dynamic SQL if the countries are not absolutely fixed for all eternity. I have no idea what marydan's query was supposed to do.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW, googling "php pivot" and "php crosstab" returns lots of hits. I've not used php, but this is how I do things in other reporting environments - ASP.NET, reporting srevices, Access etc.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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