September 20, 2011 at 11:21 am
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.
September 20, 2011 at 11:22 am
On sql 2000?
Sql 2008 is only months away from end of support (assuming no extended contract).
I would really start learning on sql 2008 R2 if I were you.
This works on 2k5+, not on 2k.
SELECT
T.name,
STUFF((
SELECT
',' + name
FROM
sys.columns C
WHERE C.object_id = T.object_id
ORDER BY
name
FOR
XML PATH('')
) , 1 , 1 , '') As Columns_
FROM sys.tables T
ORDER BY name
September 20, 2011 at 11:29 am
I understand, however, I work for the state and at this point in time, I do not have access to any other versions. There is talk of upgrading after the first of the year, however for now, I have to work with 2000.
Thanks for your help.
September 20, 2011 at 11:37 am
keith_stramler (9/20/2011)
I understand, however, I work for the state and at this point in time, I do not have access to any other versions. There is talk of upgrading after the first of the year, however for now, I have to work with 2000.Thanks for your help.
Actaully, why don't you try it on sql 2K (changing the system objects). I'm not 100% sure it would fail all of a sudden.
September 20, 2011 at 11:48 am
I did try it, but it did fail. It came with a error for incorrect syntax at FOR XML Path.
Thanks for trying to help.
September 20, 2011 at 11:52 am
keith_stramler (9/20/2011)
I did try it, but it did fail. It came with a error for incorrect syntax at FOR XML Path.Thanks for trying to help.
Ya that was my 0.1% error :-).
Anywho, I'll let you turn this into a udf. Couldn't find anything in the script section here!
DECLARE @UDFInputPAram INT
SET @UDFInputPAram = 4
DECLARE @cc VARCHAR(8000)
SET @cc = ''
SELECT @cc = @cc + name + ',' FROM dbo.syscolumns WHERE id = @UDFInputPAram
--return
SELECT STUFF(@cc, LEN(@cc), 1, '') AS Output_udf
PRINT @cc
September 20, 2011 at 11:59 am
I did try this code and it somewhat worked.
DECLARE @attach varchar(15)
DECLARE @list varchar(8000)
select @attach=attachid,
@list=program_id_name + ',' + COALESCE(@list,'')
from #t1
SELECT LEFT(@attach,LEN(@attach))as attachid, LEFT(@list,LEN(@list)-1)as progname
However, it took all of the program_id_names and put on one row with only one attachid.
How can I break out the program_id_names to match each of the correct attachids?
Thanks.
September 20, 2011 at 2:21 pm
You need to use my code to make a function.
Then call the function once for each programid
Select programid, function(programid) FRom dbo.table
Make sure you get only 1 row per id otherwise you call the function more than 1 per id which is a real perf killer.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply