SQL Server Performance

Concatenating data against a row

Discussion in 'General Developer Questions' started by sqldev, Jun 25, 2009.

  1. sqldev New Member

    Hi,
    I want to concatenate data from Field2 by grouping against Field1 data. I have provided below the sample data,
    from this I want to get output like,
    1, 'ABC/XYZ/123'
    2, 'TEST1/TEST2'
    I do not want to do pivoting as well, since it involves hard coding field's data. I want to do it dynamically using
    a single query itself.
    I have given below the script I used to get the same using CURSOR.
    Please give me the other alternatives available for the same.
    create table tmp_dev_t1
    (f1 int,
    f2 varchar(10))
    insert into tmp_dev_t1
    values(1, 'ABC')
    go
    insert into tmp_dev_t1
    values(1, 'XYZ')
    go
    insert into tmp_dev_t1
    values(1, '123')
    go
    insert into tmp_dev_t1
    values(2, 'TEST1')
    go
    insert into tmp_dev_t1
    values(2, 'TEST2')
    go
    DECLARE @Cur_t1 CURSOR, @f1 int, @v_result varchar(100)
    SET @Cur_t1 = CURSOR FAST_FORWARD FOR SELECT DISTINCT f1 from tmp_dev_t1
    OPEN @Cur_t1
    FETCH NEXT FROM @Cur_t1 INTO @f1
    WHILE @@FETCH_STATUS=0
    BEGIN
    SET @v_result = ''

    SELECT @v_result = @v_result + f2 + ',' FROM tmp_dev_t1 a
    WHERE f1 = @f1

    PRINT @f1
    PRINT @v_result

    FETCH NEXT FROM @Cur_t1 INTO @f1
    END
    CLOSE @Cur_t1
    DEALLOCATE @Cur_t1
    Thank you.
  2. FrankKalis Moderator

  3. Madhivanan Moderator

Share This Page