SQL Query help

[kyledunn]

I have two tables that have been joined into a single table. The first table contains primary key Customer Number and Customer Name. The second table contains a foreign key Customer Number and Location Number. There can be multiple locations for each customer. When I join these two tables with

Select Table1.CustomerNumber, Table1.CustomerName, Table2.LocationNumber from Table1, Table2 where Table1.CustomerNumber = Table2. CustomerNumber

I get a new table with three columns, Customer Number Customer Name and Location with a repeating Customer Number and Name for each location.

Is it possible with a query to turn this into a table with a single row for each customer number and name with a column for each location? The total number of columns would be determined by the customer with the most locations and those with fewer locations would have a null value for the unfilled columns.

Here is the example results:

Start with:

Cus# Name Location
1 John 1
1 John 2
1 John 3
2 Mike 4
2 Mike 5
3 Bob 6

With miracle query:

Cus# Name Loc1 Loc2 Loc3
1 John 1 2 3
2 Mike 4 5 null
3 Bob 6 null null

Thanks for any help!

Kyle

 

[tlbroadbent]

Do a keyword search in this forum for crosstab. You'll find plenty of examples. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ClaireHCM]

Here is the example how to do your query.
Though somebody has a long signature to go through all the faq's for this, i suggest you to take a look at your own problem solution.

select cus#,name,
sum(case location when 1 then location when 4 then location when 6 then location else null end )as loc1,
sum(case location when 2 then location when 5 then location else null end )as loc2,
sum(case location when 3 then location else null end )as loc3
from cus1
group by cus#,name
order by cus#,name

B-)

 

[tlbroadbent]

Claire,

Have you bothered to read the FAQs? Perhaps you would understand the purpose of the FAQs and Keyword Search if you took the time to become acquainted with the site. faq183-3179 is titled "Learn to Use Tek-Tips Features." faq183-874, is "Suggestions for Getting Quick and Appropriate Answers."

I welcome you and your input to Tek-Tips but encourage you to use the site to its fullest potential. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ClaireHCM]

Terry,

FAQ is becoming like BOL.People who are involed in projects,do you think they dont know about BOL or FAQ.I believe the main objective of any forum is to help others with quick example rather than redirecting to somewhere else.

 

[MAK9974]

I agree with Claire.

Single line answer and re-directing the user to a different link and/or to BOL are not going to be a help.

I do notice few personnel answering like "refer BOL" or "refer FAQ".

It is not healthy for a good forum like this.

No offense. Just my opinion.

 

[kyledunn]

Thanks for all the help! I thought I'd share my experience as a result of your posts. While I have been programming for years, I am relatively new to SQL. The first post from Terry introduced me to the term crosstab. While I didn't find alot of examples in the forum as he suggested, it did give me the keyword I needed to research the subject. All the words I had guessed led me nowhere. Often I find all I need is a clue and then I'm on a path to find an answer to a problem. Because I have great respect for an individual's time, I would like to encourage those that can effeciently hand out clues to continue to do so. My search led me to an excellent artile on the SQL Server Magazine website about Dynamic Crosstab Queries. Here is the url:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

I created the example above to illustrate what I thought would help me solve my problem when in fact I needed the crosstab query to be dynamic. At the time I had no clue that it mattered. The example Claire gave was clear and consise and certainly solved the problem that I had proposed. Had it been my only source it certainly would have led me closer to the dynamic crosstab query solution.

Thank you both for being contributors in this world. I appreciate the input from both of you.

Kyle

 

[tlbroadbent]

Kyle,

I just did a search for crosstab in this forum and the search returned several pages of results. The keyword search has a default time limit of 7 days. Choosing one of the other date range options will usually result in more finds.

ClaireHsu and Mak9974,

You can disagree but I will continue to use and help others use all of the Tek-Tips features. These features were implemented at the recommendation of Tek-Tips members.

When someone performs a keyword search and returns several threads, they can benefit from multiple existing answers and discussions. As I noted above, there are multiple threads on crosstab queries in this forum. These contain numerous examples and links to other sites, including links to the dynamic query article that Kyle posted. (For example, see thread183-400202) Simply posting an answer isn't nearly as healthy or beneficial as helping members use Tek-Tips to its fullest potential. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[kyledunn]

Terry,

I just tried the advanced search feature again and got the same results as you. I had used it incorrectly before which resulted in the limited returns. Thanks for clearing that up for me.

Kyle

 

[SQLSister]

ClaireHsu and Mak9974,
Agree with Terry, the FAQs are there for a reason - because the question comes up repeatedly. Some people either don't know they are there (I didn't at first.) or don't want to use them because they want to be spoonfed their answer.

It is actually better for your development as a programmer to read and understand what the FAQ is doing and learn to apply it to your own situation than to just be spoonfed answers that you use with little or no understanding as to what you did or why.

If you've read the FAQ or looked at other threads (it is hard to know sometimes what to search for)and still can't solve the problem we are usually happy to try to help. But answering questions is time-consuming and those of us who answert them generally have our own jobs to do to, so it is more cost effective for us to send you to the FAQ if we know one exists.

To my mind the purpose of the forum is to help everyone become better at their jobs not to solve their problems instantly with no effort on their part. So sometimes I'll discuss why something is a problem (especially bad design or use of cursors) or point them to Books online, other threads, and FAQs rather than just telling them how to do what they want to do. I've been a teacher and I know that people retain the solution better if they figure it out for themselves than if they are simply told the answer.

 

[ClaireHCM]

Terry,
As you say anybody can disagree or agree on your opinion.But what for you want to stop others using different method to help ?For one problem there maybe thousands way to solve it.You provide one way,it doesnt mean the ways that others provide are wrong or useless.

This is a open forum.Whenever someone has doubt and someone want to provide answer,everybody has every right to post the way the think.

A forum is healthy or not healthy cant be decided by one or two persons.Have you ever met somebody just want to againt whatever you say?No matter it's right or wrong.I thought in an forum even if you have different opinion,you should use polite way to reply instead of replying "you dont need to do this again because I already answered".
As long as it provide right way and it works,I dont see any wrong in answering by "Check FAQ" or "directly provide answer".For your point of view,my way of doing is wrong.But you can keep it in mind, no need of publishing it.

I believe whoever comes to forum and answer question has good intension to help others.Just try to respect other's posting.Let others to decide which way they want to take.

 

[sunila7]

Hi Claire,

When we post answers to questions which can be found by just doing a search on this site, we r putting addtional strain on this great site for its maintenance(From which we all benefit), so it always better to redirect to an answer which is already there in the FAQ or in another thread.

And as SQLsister said it is always better to find an answer on ur own than getting it spoon fed for 2 reasons. one, for the thrill of finding something and two, As SQLSister said u remember it better when u find it on ur own.


Sunil