concatenation of Records

• Anthony Malone

  Mr or Mrs. 500

  Points: 538

  More actions

  December 16, 2003 at 9:05 am

  #83612

  I Have a concatenation Problem. I have a table That captures notes from users input. ID Field is a Datetime Field, Notes Field CHAR(70), Sequence_No Field smallint. The problem lies in that That the Notes Field are split into Different Records only identified by the ID and Sequenced by the Sequence_NO. I need to know how to concatenation all Similar Records ro Create one record. E.g.

  ID Datetime,

  Input_DT Datetime,

  Notes CHAR(70),

  Sequence_NO Smallint

  Record 1 Values

  01/01/3075 12:00:58 AM, 01/23/2003 3:35:17 PM, 'Plane left at 3 P.M. Passenger was late for', 1

  Record 2 Values

  01/01/3075 12:00:58 AM, 01/23/2003 3:35:17 PM,'Plane and Got to Counter 15 minutes Late', 2

  Record 3 Values

  01/01/3075 12:00:58 AM, 01/23/2003 3:35:17 PM,'Passenger asked to be placed on Next Flight', 3

  I Hope This is Not confusing. I need to concatenate the records to rebuild the entire nmessafe that has been broken into many Records identified by the ID Field And ordered by the sequence_No Field.

  Anthony Malone

  
  Anthony Malone

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  December 16, 2003 at 9:12 am

  #486427

   
  
  -- For one record at a time... 
  
  Declare @MessData Varchar(8000) 
  
  Set @MessData = '' 
  
   
  
  Select @MessData = @MessData + ' ' + Rtrim(Notes)  
  
  	From  
  
  	Where ID = '01/01/3075 12:00:58 AM' 
  
  	And Input_DT = '01/23/2003 3:35:17 PM' 
  
  	Order by Sequence_NO 
  
  
  
  Select ID, Input_DT, @MessData
  
  	From  
  
  	Where ID = '01/01/3075 12:00:58 AM' 
  
  	And Input_DT = '01/23/2003 3:35:17 PM' 
  
  	Order by Sequence_NO 
  
   

  
  
  Once you understand the BITs, all the pieces come together

• David Burrows

  SSC Guru

  Points: 65111

  More actions

  December 16, 2003 at 10:46 am

  #486461

  You can do it using a temp table

  CREATE TABLE #temp ([ID] datetime,Notes varchar(7000)) 
  
  INSERT INTO #temp SELECT DISTINCT [ID} from  
  
  DECLARE @max smallint,@Sequence_No smallint 
  
  SELECT @max = MAX(Sequence_No) FROM  
  
  SET @Sequence_No = 0 
  
  WHILE (@Sequence_No < @max) 
  
  BEGIN 
  
  SET @Sequence_No = @Sequence_No + 1 
  
  UPDATE t 
  
  SET t.Notes = t.Notes + ' ' + CAST(a.Notes as varchar) 
  
  FROM #temp t 
  
  INNER JOIN  a ON a.[ID] = t.[ID] AND a.Sequence_No = @Sequence_No 
  
  END 
  
  SELECT * FROM #temp 
  
  DROP TABLE #temp 
  
   

  Far away is close at hand in the images of elsewhere.
  Anon.