Aggregating Varchar fields

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike2265 Right there with Babe Points: 753 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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)