What started out as a very innocent SQL-related question quickly expanded into an abominable Frankensteins Monster.

One of my coworkers asked if there was a way to create a SQL View in SQL 2000 so that when queried it would spit out a constituent’s ID and then list, in comma-delimited fashion the years they won a degree from WMU’s Haworth College of Business, in earlier-then-later year order. So she wanted:

0000045321  1968, 1974, 1978

Here is how I did it. I created a Frankensteins Monster. It’s two views and a function. I have unleashed this abomination upon the face of the Earth and I feel a lot like Dr. Frankenstein, maniacally dry-washing my hands and laughing “Muhahahahaha!” a lot. It doesn’t take much to get that out of me. 🙂 The code is beyond the More… tag, if you care to expose yourself to the abomination…

— Create our first view so we can abstract away the complicated criteria to get the group we seek, hcob people, at WMU.

create view ajm_hcob_degreeyears as select schlid,schldegyr from school where schlinstit=’mi91′ and schlschool=’b’

— Function to take in a schlid, then scan all the schldegyr’s and place them in comma-delimited order, earliest to latest.

CREATE FUNCTION dbo.fnMakeList

(  @SchoolID int )

RETURNS varchar(1000)

AS

BEGIN

DECLARE @TempSchool table

(  degyear varchar(20)  )

DECLARE @SchoolList varchar(1000)

SET @SchoolList = ”

INSERT INTO @TempSchool

SELECT schldegyr

FROM ajm_hcob_degreeyears

WHERE schlid = @SchoolID

ORDER BY schldegyr

IF @@ROWCOUNT > 0

UPDATE @TempSchool

SET @SchoolList = ( @SchoolList + degyear + ‘, ‘ )

RETURN substring( @SchoolList, 1, ( len( @SchoolList ) – 1 ))

END

— View to abstract away the grouping mechanics

create view ajm_hcob_degreelist as

SELECT schlid, DegreeList = dbo.fnMakeList( schlid ) FROM ajm_hcob_degreeyears GROUP BY schlid

select top 500 * from ajm_hcob_degreelist

— cleaning everything up if what is above isn’t needed anymore

drop function dbo.fnMakeList

drop view ajm_hcob_degreelist

drop view ajm_hcob_degreeyears