Skip to main content

Coalesce

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)
RETURNS VARCHAR(1000) AS

BEGIN
DECLARE @FishList varchar(1000)

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

RETURN @FishList
END


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


Result:
StreamParentFishSurveyed
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.)
...

Link

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

Labels:

Comments

Post a Comment

Popular posts from this blog

Auto Format (and Color) Outlook Appointments

A few years ago I got turned on to the idea of indexing your life by color. In a quick glance your mind can comprehend really complex patterns. By coloring entries in my calendar I am able to tell immediately if I am available or if a future appointment conflicts with a work meeting. There are a number of ways to set this up. Outlook allows you to add a label to every appointment. However this is an Outlook specific feature and I sync my calendar across Outlook, Yahoo! and iCalendar. The later two don't even have labels. Besides, calendars should be simple. Complexity only hinders usability, so I prefer an automated solution. How to color appointments in Outlook automatically: In Calendar, right-click the calendar grid, and then click Automatic Formatting on the shortcut menu. Click Add, and then type a name for the rule. In the Label list, click a color. Click Condition to specify the conditions under which the color will be applied. Note: If you manually assign a color to a
1 comment
Read more

Attachment Reminder - and more for MS Outlook

I just did it again. We don't like to admit it, but we all have. You write a long letter describing the attachment, press send and then 10 seconds later remember you didn't actually attach the message. I finally decided to do something about it. Turns out it isn't too hard. Chiefly because Jimmy Peña at Code for Excel and Outlook already did all the hard work of writing up an excellent MS Outlook Etiquette Check Macro that does all the dirty work for you. What's left for you to do? In MS Outlook go to Tools > Macros > Visual Basic Editor Under the Project Panel (far left) Browse to Project1 > Microsoft Office Outlook Objects > ThisOutlookSession Double-click ThisOutlookSesson to Open (if you haven't been here before this will be a big blank canvas) Visit Code for Excel and Outlook Etiquette Check Code and select "Copy to Clipboard" at the top of the code. Or you can also copy from the code I've modified below if you prefer.
5 comments
Read more

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 http://olddomain.com/content to http://mydomain.com/content. Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console. In the HTTP Redirect Dialog: Check Redirect requests to this destination Enter your new path ending with $V$Q.  e.g. http://mydomain.com$V$Q Counter-intuitively check Redirect all request to exact destination (instead of relative destination) Choose the appropriate Status Code (Permanent or Temporary)
5 comments
Read more