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 |
keith2601
Starting Member
7 Posts |
Posted - 2011-09-20 : 15:10:35
|
Hello,I am just now learning SQL on SQL2000. I need to concatenate multiple rows into one column. I have looked for examples, but do not find one that I can use to suit my needs. Since I am on 2000, XML will not help and also, I will have possibly hundreds of rows in the final draft, so, I cannot do a insert using the data or unions using the data.Here is an example of the data:attachid program_id001 MCP001 PES001 QRS002 DAV002 EFG003 ADK003 BCC003 HRRThe results I want areattachid program_id001 MCP, PES, QRS002 DAV, EFG003 ADK, BCC, HRRAs I said, I could potentially have hundreds of attachids, so inserts or unions would not work if you have to use the data.Any help would be appreciated. Thanks so much. |
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
|
keith2601
Starting Member
7 Posts |
Posted - 2011-09-20 : 15:25:34
|
Unfortunately, XML does not work for us. Thanks for your suggestion though. |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
|
keith2601
Starting Member
7 Posts |
Posted - 2011-09-20 : 15:39:47
|
Again, I see the use of XML and also using the Union option. Using the UNION option is the insert of data. I have too much data to try and use inserts or unions. I tried this code:DECLARE @attach varchar(15)DECLARE @list varchar(8000)select @attach=attachid,@list=program_id + ',' + COALESCE(@list,'') from #t1SELECT LEFT(@attach,LEN(@attach))as attachid, LEFT(@list,LEN(@list)-1)as prognameThis code took all of the program_ids and put them on one row, but it matched it to only one attachid. How can you get it to match the multiple program_ids to each of the attachids?I appreciate your help. |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2011-09-20 : 15:58:25
|
Hello,Here is an example of the recent method I was referring to:--create container for workCREATE TABLE #t ( id INT IDENTITY(1,1) PRIMARY KEY, attachid CHAR(3), program_id CHAR(3), customVal VARCHAR(100) DEFAULT '')--insert sample dataINSERT #t (attachid, program_id)SELECT '001','MCP' UNION ALLSELECT '001','PES' UNION ALLSELECT '001','QRS' UNION ALLSELECT '002','DAV' UNION ALLSELECT '002','EFG' UNION ALLSELECT '003','ADK' UNION ALLSELECT '003','BCC' UNION ALLSELECT '003','HRR'--declare varsDECLARE @customVal varchar(100), @lastAttachID char(3)--init vals SELECT @customVal = '', @lastAttachID = 0--roll up the valsUPDATE #tSET @customVal = customVal = CASE WHEN @lastAttachID != attachID THEN program_id ELSE @customVal + ', ' + program_id END, @lastAttachID = attachid--display resultsSELECT t.attachid, customVal AS program_idFROM #t tJOIN( SELECT attachid, MAX(id) id FROM #t GROUP BY attachid ) d ON d.id = t.idORDER BY t.id ASC--clean upDROP TABLE #t HTH. |
|
|
keith2601
Starting Member
7 Posts |
Posted - 2011-09-20 : 16:05:06
|
I understand what you are doing here. Unfortunately, it is not feasible to use inserts or unions. I would potentially have hundreds of attachids in the table. This is why I asked the question here. I looked all over google results and found the same type of suggestions you are making. Those would just not work as attachids would be updated or appended constantly in the table by users. If you have any other suggestions besides using XML, Union scripts or insert scripts I would certainly appreciate it. But, thanks for your help anyway. |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2011-09-20 : 16:07:13
|
quote: Originally posted by keith2601 I understand what you are doing here. Unfortunately, it is not feasible to use inserts or unions. I would potentially have hundreds of attachids in the table. This is why I asked the question here. I looked all over google results and found the same type of suggestions you are making. Those would just not work as attachids would be updated or appended constantly in the table by users. If you have any other suggestions besides using XML, Union scripts or insert scripts I would certainly appreciate it. But, thanks for your help anyway.
Sorry I could not be of more help for you. I hope you find a solution which fits your need. Best wishes. |
|
|
|
|
|
|
|