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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 concatenate multiple rows to one column

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_id
001 MCP
001 PES
001 QRS
002 DAV
002 EFG
003 ADK
003 BCC
003 HRR

The results I want are

attachid program_id
001 MCP, PES, QRS
002 DAV, EFG
003 ADK, BCC, HRR

As 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

Posted - 2011-09-20 : 15:19:00
Hello,

A technique for performing such tasks was discussed earlier today here;

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165635

Perhaps it can help you.

HTH.
Go to Top of Page

keith2601
Starting Member

7 Posts

Posted - 2011-09-20 : 15:25:34
Unfortunately, XML does not work for us.

Thanks for your suggestion though.
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-20 : 15:28:27
My apologies, I did not read your OP carefully. Here is another option for your consideration:

http://visakhm.blogspot.com/2010/03/using-quirky-updates-to-develop-well.html
http://www.sqlteam.com/article/converting-multiple-rows-into-a-csv-string-set-based-method

A recent sample implementation can be found here;

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165569

HTH.
Go to Top of Page

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 #t1

SELECT LEFT(@attach,LEN(@attach))as attachid, LEFT(@list,LEN(@list)-1)as progname

This 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.
Go to Top of Page

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 work
CREATE TABLE #t
(
id INT IDENTITY(1,1) PRIMARY KEY,
attachid CHAR(3),
program_id CHAR(3),
customVal VARCHAR(100) DEFAULT ''
)


--insert sample data
INSERT #t (attachid, program_id)
SELECT '001','MCP' UNION ALL
SELECT '001','PES' UNION ALL
SELECT '001','QRS' UNION ALL
SELECT '002','DAV' UNION ALL
SELECT '002','EFG' UNION ALL
SELECT '003','ADK' UNION ALL
SELECT '003','BCC' UNION ALL
SELECT '003','HRR'


--declare vars
DECLARE
@customVal varchar(100),
@lastAttachID char(3)


--init vals
SELECT
@customVal = '',
@lastAttachID = 0


--roll up the vals
UPDATE #t
SET
@customVal = customVal =
CASE
WHEN @lastAttachID != attachID THEN program_id
ELSE @customVal + ', ' + program_id
END,
@lastAttachID = attachid


--display results
SELECT
t.attachid,
customVal AS program_id
FROM #t t
JOIN
(
SELECT attachid, MAX(id) id
FROM #t
GROUP BY attachid
) d ON d.id = t.id
ORDER BY t.id ASC


--clean up
DROP TABLE #t


HTH.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -