Multiple records into one row.

• rickt

  Grasshopper

  Points: 14

  More actions

  February 15, 2007 at 4:22 am

  #171448

  Hi,

  I have the following table structure, which I can't change

  ID          Text

  1            Text Line 1

  1            Text Line 2

  2            Text Line 1

  2             Text Line 2

   

  What I can;t seem to do is get a recordset that looks like this

  ID     Concatonated Text file

  1       Text Line 1  Text Line 2

  2       Text Line 1  Text Line 2

   

  There could be a large and varying number of records per ID

   

  Any help would be appreciated.

  Thanks

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  February 15, 2007 at 6:35 am

  #689922

  What do you mean by 'large' and what is the datatype of 'Text'

  The usual way to do this is to use a function

  CREATE FUNCTION dbo.udf_text (@ID AS int) RETURNS varchar(8000)

  AS

  BEGIN

  DECLARE @outstring varchar(8000)

  SELECT @outstring = COALESCE(@outstring + ' ' + [Text] ,[Text])

  FROM 

  WHERE [ID] = @ID

  RETURN ISNULL(@outstring,'')

  END

  SELECT [ID], dbo.udf_text([ID]) AS [ConcatonatedText]

  FROM

  this is limited to a total of 8000 chars per ID

   

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

• rickt

  Grasshopper

  Points: 14

  More actions

  February 15, 2007 at 7:47 am

  #689944

  Thank You. It worked a treat.