January 3, 2012 at 4:34 pm
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
This is pretty vague. Perhaps you should lookup the WHERE clause?
January 3, 2012 at 4:55 pm
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
;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
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
Change is inevitable... Change for the better is not.
January 4, 2012 at 7:03 am
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