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 |
|
decem
Starting Member
21 Posts |
 Posted - 2005-08-17 : 15:45:49
|
| Hello. I think this is a quick and easy question to answer. I hope it is anyway. It's kind of hard to write the question out however, so please bear with me. I have two tables with the following columns and data: Table1 Columns: Name - City - StateData: Bob - Vallejo - CAData: Jimmy - Cleveland - OHTable2Columns: Name - NotesData: Bob - Note1Data: Jimmy - Note1Data: Bob - Note2Data: Bob - Note3I want to join those table and create a table that looks like this:Table3Columns: Name - City - State - NotesData: Bob - Vallejo - CA - Note1, Note2, Note3The join part is easy enough, but how do I create that Notes column where it pulls from many rows in Table2 and puts all that data into just one row in Table3?Thank you. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-17 : 17:42:23
|
| Why would you want to do this? How big are those notes? How many notes could there be for a single person? Isn't there a good reason that your tables were designed the way they are?If you still insist on doing this, you'll need a variation on Garth's article on Coalesce. We had another post here on the site not long ago essentially asking the same question. I can't find it right now, but you might want to do some searching.---------------------------EmeraldCityDomains.com |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
|
|
decem
Starting Member
21 Posts |
Posted - 2005-08-18 : 11:01:51
|
| Thanks Mark. That worked.. sort of.. I did this:DECLARE @notes varchar(255)SELECT @notes = COALESCE(@notes + ', ', '') + CAST(note AS varchar(25))FROM Table2WHERE Name = BobSELECT @notesand it returned one column with this data: "note1, note2, note3", which is exactly what I need, but now how do I combine that result with Table1 so as to return: "Bob - Vallejo - CA - Note1, Note2, Note3"?Thanks again! |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-18 : 14:30:22
|
| You could put that in a function and then pass in the WHERE clause value. Then use that function like SELECT Name, City, State, dbo.MyFunction(Name) FROM Table1But I think that second thread I posted may have some better ideas.---------------------------EmeraldCityDomains.com |
|
|
|
decem
Starting Member
21 Posts |
Posted - 2005-08-18 : 15:20:34
|
| Sorry Mark.. I'm still at a loss.. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
|
|
|
|
|
|
|
Join question