Aggregating Varchar fields

• jonkle

  SSC Rookie

  Points: 26

  More actions

  June 3, 2002 at 11:40 am

  #78769

  Has anyone managed to aggregate varchar data like this.

  id name

  --------------

  1 John

  1 Steve

  1 Lars

  1 Sven

  id name

  -----------------------------

  1 John, Steve, Lars, Sven

  I'm missing a summarizing function for text fields in SQL Server.

  Any help will be greatly appreciated.

  Regards,

  Jonas

• Mike2265

  Right there with Babe

  Points: 753

  More actions

  June 3, 2002 at 7:30 pm

  #430283

  The is a real elegant way of doing it and very flexible. I use it quite a bit to return detailed messages and also to create T-Sql which has varying columns (eg pivot tables).

  declare @STR varchar(1000)

  select @STR = coalesce(@str,'') + ColName + ', '

  from tablename

  select @STR

  Hope that helps

  cheers,

  Mike

• Antares686

  SSC Guru

  Points: 125444

  More actions

  June 3, 2002 at 7:41 pm

  #430285

  Same as previous except extended a bit, was doing when other came thru so didn't see.

  This is a concept piece so I really haven't tested, but should be sound.

  DECLARE @ctrlLoop int

  DECLARE @maxLoop int

  DECLARE @pivname varchar(8000)

  CREATE TABLE #tmpOutput (

  [unid] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

  [curid] [int] NOT NULL,

  [pivname] [varchar](8000) NULL

  )

  /*

  Note: do not use GO as it will cause the temp table to go out of scope.

  */

  --Prebuild our temp table with control data.

  INSERT INTO #tmpOutput (curid) SELECT DISTINCT [id] FROM urTbl

  --Set main variables

  SET @ctrlLoop = 0

  SET @maxLoop = MAX(unid) FROM #tmpOutput

  WHILE @ctrlLoop < @maxLoop

  BEGIN

  SET @ctrlLoop = @ctrlLoop + 1 --prepare to move thru data

  --Preset @pivname to blank

  SET @pivname = ''

  --All data will be concatinated together into single variable.

  SELECT @pivname = @pivname + (CASE WHEN LEN(@pivname) > 0 THEN ',' END) + [name]

  FROM urTbl WHERE [id] IN (SELECT curid FROM #tmpOutput WHERE unid = @ctrlLoop)

  --Update pivname value for temp table based on curid.

  UPDATE #tmpOutput SET pivname = @pivname WHERE unid = ctrlLoop

  END

  SELECT curid, pivname FROM #tmpOutput

  /*

  Note: At this point you could output to a permanent table so you don't have to redo every time. And even make a trigger to keep updated.

  */

  DROP TABLE #tmpOutput

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  Edited by - antares686 on 06/03/2002 7:42:19 PM

• jonkle

  SSC Rookie

  Points: 26

  More actions

  June 4, 2002 at 5:54 am

  #430298

  Thanks for the help.

  I tried the first example (because it looked simple) but how do I incorporate

  declare @STR varchar(1000)

  select @STR = coalesce(@str,'') + name + ', '

  from tbl

  select @STR

  ...into one query where I want to summarize many ids. Creating and updating a separate table with the information is not the ideal solution for me.

  /Jonas

• Antares686

  SSC Guru

  Points: 125444

  More actions

  June 4, 2002 at 6:13 am

  #430299

  You can't without many variables and unions to join them when done or doing as I suggested with the temp table to summarize the data. I only suggest use a permanent table in case this will run often so that you do not have to query this repeatedly, thus cutting down on server overhead.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)