I had a cool little SQL problem yesterday where I had a table with 2 columns: cn, email and for some reason there could be many entries for the same email address:

cn | email

1 | [email protected]

23 | [email protected]

So I needed to get a nice query where I could join all the values of the cn column into a single column for the same email. This was tricky but custom SQL functions to the rescue. This is for MSSQL but you can tweak it for MySQL very easily. So the first thing is to create a function:

CREATE FUNCTION [dbo].[getValueList]

(@email varchar(500))

RETURNS varchar(500)

AS

BEGIN

DECLARE @listStr VARCHAR(500)

SELECT @listStr = COALESCE(@listStr+',' , '') + cast(cn as varchar)

FROM tempTokenMailingList

WHERE emailAddress = @email

RETURN @listStr

END

Once I did this, it will return to me a list of column values based on an incoming email on my target table. You can make this more dynamic but it basically solves my use case. Then on my selection I do this:

select distinct emailAddress, name, token,

( dbo.getValueList(emailAddress) ) CN

from tempTokenMailingList

order by emailAddress

Run that and VOILA! You get a cool way to get your cn column concatenated as a list of values.