Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
keefer
Starting Member
3 Posts |
 Posted - 2003-03-17 : 12:26:06
|
I got a small problem with SQL Queries in SQL Server. I am doing some searching and I need to combine some table fields before I can search them. I have an Education table. With EducationID as the primary key. My users, have a username that gets stored with the EducationID. Therefore, one user can have multiple EducationId's For ExampleEducation Table__________________________________________| EducationID | username | school ||____________| _________|__________________|| 1 | jeff | penn state || 2 | jeff | rutgers || 3 | greg | penn state || 4 jeff | cmu || 5 | greg | pitt ||______________________|__________________|This being the case, is there away to use a union or some type of join or anythign else so that I can combine all of jeff's and all of gregs Education into one table? So that I getJeff [ Penn State Rutgers, CMU] andGreg [Penn State PItt] It doesn't matter if I lose the educationID column or not, or wheter it gets combined too.Anyone offer suggestions? It maynot be the most efficient, but thats what we need todo.Thanks  |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-17 : 12:34:29
|
| When you say "combine" do you mean combine into a single string that is to become the value of a single column?if so... Converting Multiple Rows into a CSV String (Set Based Method) ... and I'll save Jeff the trouble of posting and mention that if you are on SQL2K, make sure you read the comments to the article and especially byrmol's function.Jay White{0} |
 |
|
|
keefer
Starting Member
3 Posts |
Posted - 2003-03-17 : 12:51:17
|
| What I mean by combine is that I want to put all of Jeff's school's into one table field. As in tak eall of his EducationId and send it to a new table or whatever so that it all gets merged into one field and his name once in other field.As such, the new table or view or stored procedure would show that Username | School------------------------------------Jeff | Penn State, Rutgers, etc.Greg | Penn state, PittIf I can't do this, then whenI search this, I get Jeff's name displayed 3 times, not once and Greg's name 2 times. And this makes our search results frustrating, because the same person is in it like 3-4 times or more. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-17 : 13:15:09
|
| I'm pretty sure if you actually read the article I pointed you to, it does exactly what you are requesting.Have you considered other ways of getting your search results without duplicates. Something along the lines of a DISTINCT or a GROUP BY?Jay White{0} |
|
|
|
keefer
Starting Member
3 Posts |
Posted - 2003-03-17 : 13:54:41
|
| Basically, I think I am trying todo something like http://www.sqlteam.com/item.asp?ItemID=796 but I'm not sure how all the ranking comes into play, and how it can be adapted for my sql tables. |
|
|
|
|
|
|
|
|
Combine