Post reply

Query Question

  • January 3, 2012 at 4:34 pm

    #262348

    Hello all, I hope I explain this correctly. I need to figure out how to query my DB to get a certain result set back. As an example, if there was a national database of retailers, I would need a query which returned me:

    Best Buy 123 ABC Street Anywhere, USA 12345

    234 ABC Street Anywhere, USA 23456

    Target 345 BCD Street ElseWhere, USA 34567

    456 CDE Street ElseWhere, USA 45678

    I am sure you get my drift. What woudl be teh best and most efficient way to accomplish this?

    Thanks,

    BK

  • January 3, 2012 at 4:44 pm

    #1428039

    This is pretty vague. Perhaps you should lookup the WHERE clause?

  • January 3, 2012 at 4:55 pm

    #1428042

    Sorry If I was vague, basically I want to return only the Best Buy, but with all the addresses associated with it. I know how to use a WHERE caluse, but that will not get me what I want. I want best buy to show in the results set once, with however many addresses are associated with to come back as well.

  • January 3, 2012 at 5:00 pm

    #1428043

    ;with rowCte as

    (

    select *, row_number() over (partition by store_name order by store_name) [num]

    from allStores

    )

    select case when num = 1 then storeName else '' end storeName, storeAddress

    from rowCTE

    Something like that? There is maybe a more clever way, this was just my first thought.

  • January 4, 2012 at 4:56 am

    #1428186

    William Krupinsky JR (1/3/2012)

    Hello all, I hope I explain this correctly. I need to figure out how to query my DB to get a certain result set back. As an example, if there was a national database of retailers, I would need a query which returned me:

    Best Buy 123 ABC Street Anywhere, USA 12345

    234 ABC Street Anywhere, USA 23456

    Target 345 BCD Street ElseWhere, USA 34567

    456 CDE Street ElseWhere, USA 45678

    I am sure you get my drift. What woudl be teh best and most efficient way to accomplish this?

    Thanks,

    BK

    For the second and fourth row of your expected output above, does the database actually have the company name and you've just hidden it? If so, then Rory's code suggestion should do the trick just fine. If not, then a bit more detail will be needed to answer your question.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 4, 2012 at 7:03 am

    #1428274

    This is a presentation issue and is best handled in the presentation layer, not the database layer. You don't mention what you are using for your presentation layer, but SSRS and Excel can both easily handle this type of formatting.

    Drew

    PS: You need to learn how to use the IFCode Shortcuts on the left-hand side of the message screen. Any of the [code] tags will preserve spaces in the enclosed text, which would have made your question much more obvious.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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