group by and concat dependent rows
-
got a problem: table(with names A, B): A B ---------- 1 Hallo 1 wie geht es dir? 2 schlecht! 3 nein, 3 eigentlich gut! I want the result: A B Resultat C ------------------------------------------- 1----Hallo---------------- 1----wie geht es dir?-----Hallo wie geht es dir? 2----schlecht!------------schlecht! 3----nein,---------------- 3----eigentlich gut!------nein, eigentlich nicht Thank`s for helping!!! nico
-
got a problem: table(with names A, B): A B ---------- 1 Hallo 1 wie geht es dir? 2 schlecht! 3 nein, 3 eigentlich gut! I want the result: A B Resultat C ------------------------------------------- 1----Hallo---------------- 1----wie geht es dir?-----Hallo wie geht es dir? 2----schlecht!------------schlecht! 3----nein,---------------- 3----eigentlich gut!------nein, eigentlich nicht Thank`s for helping!!! nico
Try this: First) You must create a "user defined function" (T SQL function) like this CREATE FUNCTION SSum (@a INT) //SSum = string sum RETURNS NVARCHAR(255) AS BEGIN DECLARE @ssum NVARCHAR(255) DECLARE @b NVARCHAR(25) SET @ssum = '' DECLARE crs CURSOR FOR SELECT B FROM table WHERE A = @a OPEN crs FETCH NEXT FROM crs INTO @b WHILE( @@FETCH_STATUS = 0 ) BEGIN SET @ssum = @ssum + '-' + @b FETCH NEXT FROM crs INTO @b END CLOSE crs DEALLOCATE crs IF( LEN(@ssum) != 0 ) SET @ssum = SUBSTRING(@ssum,2,255) RETURN @ssum END Second) Build a SELECT query using SSum function like this: SELECT DISTINCT A , dbo.SSum(A) AS Result FROM table --- object oriented uml oriented iconix oriented sql oriented truespace oriented --- solitare oriented :-)