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.
A SQL Server 2005 solution could be SELECT T1.f1, MIN(SUBSTRING (MyList, 2, LEN(MyList))) FROM dbo.tmp_dev_t1 T1 CROSS APPLY (SELECT '/' + T2.f2 FROM dbo.tmp_dev_t1 T2 WHERE T1.f1 = T2.f1 ORDER BY T2.f2 FOR XML PATH ('')) L (MyList) GROUP BY T1.f1 If you're still on SQL Server 2000, have a look here: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
Many approaches are listed here http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/