Skip to main content


Building on the vein of representing one-to-many relationships I've always been frustrated trying to display child records in tables and datagrids. Examples include showing multiple authors of publications, children in a classroom, and fish species observed in a lake.

Until today the two best methods I had were both hacks.
Method 1
Use Three Queries
1.) Build a crosstab
2.) Concatenate the output of the crosstab
3.) Join the product back to the parent table

- or -

Method 2
Loop through child records programmatically to build a comma-delimited list.

Today I discovered a far more elegant solution using COALESCE within a User Defined Function to build comma-delimited list in Views and Stored Procedures.

For this example we'll be building a comma-delimited list of fish species observed in each water (lakes and streams).

Step 1: Create a new User Defined Function(UDF)
CREATE FUNCTION dbo.udf_GetFishByWater(@WaterID int)

DECLARE @FishList varchar(1000)

SELECT @FishList = COALESCE(@FishList + ', ', '') + SpeciesName
FROM VU_FishSpeciesByWater
WHERE WaterID = @WaterID

RETURN @FishList

Step 2: Call the User Defined Function in a View
SELECT WaterName As Stream, Parent,
dbo.udf_GetFishByWater(WaterID) as FishSurveyed
FROM GIS_Hydrography

Abbott GulchS. Fk. Boise RiverRedbank Trout, Sculpin (Var. Species)
Adair CreekJungle CreekCutthroat Trout, Westslope Cutthroat Trout
Adams CreekMann CreekRainbow / Redband Trout (Wild), Rainbow Trout
Agency CreekLemhi RiverBull Trout, Cutthroat Trout, Rainbow Trout, Sculpin (Var. Species)
Ahrs GulchSaint Joe RiverBrook Trout, Cutthroat Trout
Airplane LakeShip Island CreekCutthroat Trout, Rainbow Trout
Alder CreekMorgan CreekCutthroat Trout, Rainbow / Redband Trout (Wild), Rainbow X Cutthroat Trout
Alder CreekPatterson CreekBull Trout, Cutthroat Trout, Rainbow / Redband Trout (Wild), Rainbow X Cutthroat Trout, Unknown Species
Alder CreekS. Fk. Payette RiverDace (Var. Sp.), Rainbow Trout, Sucker (Var. Sp.)


NOTE: These are now stored as Scalar-valued Functions in SQL Server 2008


Popular posts from this blog

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects.  It looks like it'll be as easy Apache finally.  That is until you try to redirect a querystring.  Then everything bombs.

Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect.

And How.
In our example we'll redirect all pages under to
Pick the virtual directory you want to redirect. e.g. Click HTTP Redirect under IIS in the IIS management console.In the HTTP Redirect Dialog:
Check Redirect requests to this destinationEnter your new path ending with $V$Q.  e.g.$V$QCounter-intuitively check Redirect all request to exact destination (instead of relative destination)Choose the appropriate Status Code (Permanent or Temporary)Apply Changes and Test

Resultant table not allowed to have more than one AutoNumber field

Ever get this error on a create table or insert query? The work around is quite simple. Cast the autonumber as an integer. In query design view change your field definition for one of your autonumbers to read:
XID: CInt([ID])
Or in SQL View:
(Ran into this error message again this morning and it reminded me I should share the workaround.)

Serving up KML in IIS 6

To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.

Google Earth reads KML and KMZ files. The MIME type for KML files is

* application/

The MIME type for KMZ files is

* application/

Source: Google KML Tutorial

To add a MIME type to a Web site or directory

1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

2. Click the HTTP Headers tab.

3. Click MIME Types.

4. Click New.

5. In the Extension box, type the file name extension.

6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, an…