concatenate rows (sql server 2000)

  • Hi all, I have the following problem when using sql server 2000

    The following table has around 200 records.

    Company / Employee

    1005 / A

    1005 / B

    1005 / C

    1010 / X

    1010 / Y

    1020 / L

    1020 / M

    etc etc

    I wish to create the following (comma seperated) output:

    Company / Employees

    1005 / A, B, C

    1010 / X. Y

    1020 / L, M

    etc etc

    Im having a really hard time with this in sql server 2000. while 2005 seems to offer easier solutions to solve this issue! i hope someone has a bright idea, to explain how to solve this...

  • mlandsheer (2/9/2010)


    Im having a really hard time with this in sql server 2000. while 2005 seems to offer easier solutions to solve this issue! i hope someone has a bright idea, to explain how to solve this...

    Hi,

    To set this type of cross Pivoting is, may difficult in the sql2000.

    One of the best options of this, first Concatenation of the column in the separate function and then call this function in the select table.

    create table MYTABLE

    (

    company varchar(4),

    employee varchar(2)

    )

    insert into MYTABLE

    select '1005','A'

    union all

    select '1005','B'

    union all

    select '1005','C'

    union all

    select '1006','AB'

    union all

    select '1006','BC'

    union all

    select '1006','CA'

    /*create function to concat of the column value*/

    create function dbo.concate (@company varchar(4))

    returns nvarchar(1000)

    as

    begin

    declare @concat nvarchar(1000)

    select @concat=coalesce(@concat,'')+employee+',' from MYTABLE where company =@company

    select @concat = substring(@concat,1,len(@concat)-1)

    return (@concat)

    end

    /*call the function in the select table*/

    select company,(select dbo.concate (company))as result

    from MYTABLE

    group by company

  • this has been wonderful help! thank you so much!

  • what a nice method.. saved me a cursor and additional coding..

    thx man..

    Cheers,
    John Esraelo

  • Check this approach... Simple and effective as well.

    SELECT

    S.ServiceId,

    STUFF(

    (

    SELECT ',' + [Description]

    FROM ServiceServiceFunctions SSF

    INNER JOIN ServiceFunctions SF

    ON SF.ServiceFunctionId = SSF.ServiceFunctionId

    WHERE SF.ServiceFunctionId = SSF.ServiceFunctionId

    AND SSF.Serviceid = S.Serviceid

    FOR XML PATH('')

    ), 1, 1, '') as functions

    FROM Services

    Koncentrix

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply