Have you ever wanted to concatenate a repeating column as a comma delimitated string in the same query result? Normally, I would use a Scalar Valued Function to COALESCE the values and return them to the calling query (as in the recent post here by Dustin Brisson), but there is a way to do this all in a single request.

SELECT      DISTINCT
         UserID,

         STUFF(
               (SELECT      ',' + SubTableUser.UserRole
               FROM      TableUser AS SubTableUser
               WHERE      SubTableUser.UserID = TableUser.UserID
               FOR XML PATH('')), 1, 1, '') AS UserRole

FROM      TableUser
ORDER BY   UserID

Above is a query with a sub-query. The parent query using DISTINCT against the record values that will remain the same during the repeated result set. The result set in this case happens because there are several users in this fictional database that have more than one user role assigned.

The sub-query, concatenates a comma in front of each additional result for the user role obtained when the user ID of the sub-query matches the distinct user ID of the parent query. This results in a very nice string with an awful leading comma.

You could strip out the comma whatever will display the results, but here we are using the STUFF command to take the first character, in this case a comma, and replace it with nothing. This results in a nice clean list of user roles for each distinct user ID.

If you find this post useful please leave a comment and let me know how you used the information.

Our customers have a process where the lowest level of the organization submits weekly activity statuses up the chain. Each higher level may have 1 or more reporting sub levels and would require them to compile the results before submitting to their superiors. They have, for the longest time, been using email as their means of submission. The reports would come in as either messages in the email itself or Word documents attached to the email. There was no standardization among them. They would take hours cutting and pasting the comments before submitting up the ladder.

So, they came to us asking if their was a better way. We created a web application that displays a standard entry form with categories and text fields for each level to fill out. Once they submit the form, the data is collected in the tables. As the next level logs into the web application, the data from the tables would be compiled and then displayed to the screen.

In order to accomplish this, we use a Scalar-valued Function. The function receives attributes passed from the application and returns the compiled string to display. Here is how it works:

Table
UnitID   Date      CategoryID   CategoryContent   
1   6/13/2011   1      Unit 1 Cat 1
2   6/13/2011   1      Unit 2 Cat 1
3   6/13/2011   1      Unit 3 Cat 1
4   6/13/2011   1      Unit 4 Cat 1
1   6/13/2011   2      Unit 1 Cat 2
2   6/13/2011   2      Unit 2 Cat 2
3   6/13/2011   2      Unit 3 Cat 2
4   6/13/2011   2      Unit 4 Cat 2

Inside your cfquery tag you would include the function call like this

<cfquery name="CompileComments" datasource"YourDataSource">
SELECT DISTINCT
   UnitID, dbo.fnCompileComments('6/13/2011', '1', CategoryID) AS CompiledComments
FROM   TableName
WHERE   Date = '6/13/2011' AND UnitID = 1
</cfquery>

Function name in SQL is dbo.fnCompileComments. List the variables you are passing it and then declare the return Variable with AS.

The SQL function looks like this:

USE [DatabaseName]
GO
/****** Object: UserDefinedFunction [dbo].[fnCompileComments] Script Date: 06/13/2011 13:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fnCompileComments]
(
   @MYDate DATE,
   @MYUnitID INT,
   @MYCategoryID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
   DECLARE @Comments VARCHAR(MAX)

SELECT @Comments = COALESCE(@Comments + char(10) + char(13) + '|', + '') + CategoryContent
FROM   TableName
WHERE Dater = @MyDate AND
   UnitID = @MyUnitID AND
CategoryID = @MyCategoryID

RETURN @Comments

END

The expected result:
Unit 1 Cat 1
|Unit 2 Cat 1
|Unit 3 Cat 1
|Unit 4 Cat 1

Unit 1 Cat 2
|Unit 2 Cat 2
|Unit 3 Cat 2
|Unit 4 Cat 2

The user defined function uses the COALESCE function to compile the comments together. I use a char(10) and a char(13) to put a return line and carriage return between each comment. I also include a unique character "|" that I use for a specfic function elsewhere in the application.

As I continue to grow in my programming role, I always look for faster ways of accomplishing tasks. Recently I found a useful tool within Adobe Dreamweaver which will definitely help save time for not only me but for other members of the development team I am on. This tool is the ability to create SNIPPETs and then package them into an Extension to be loaded with the Extension Manager.

My teammates and I are running a large website which we do a lot of code reuse across multiple applications. In one small example, we have gone as far as creating custom tags for buttons to standardize the look and feel for our customers throughout the website. There are many attributes associated with these buttons but there are only a few that are required. Instead of trying to remember what is required, we have created a Snippet.

What is a SNIPPET anyway and where do you find it? Dreamweaver Snippets allow you to save and reuse chunks of code over and over. The Snippet will insert the custom code into the code on your page. Actually, it will insert whatever is in the Snippet into your page. This could be custom Comment Tags, custom Forms, custom Javascript, and more. You can view the Snippets within Dreamweaver by opening the Snippets tab of the code panel. To do this, choose Window then Snippets -OR- Press Shift-F9. Dreamweaver comes pre-loaded with some Snippets. These may be helpful. But what if you don't use any that are preloaded? What if you wanted to create your own? Let me show you how.

Once you have the Snippet Panel opened, you can create a new Snippet by Clicking on the New Snippet icon below the Snippets window (it looks like a file with a plus sign) -OR- Right Click within the panel and choose New Snippet. You can also Edit or Delete a Snippet and even organize your Snippets into Folders by using the other icons or Right Click Menu options.

The Snippet window allows you to name your Snippet and give it a description. The Snippet Type determines if the Snippet is inserted into your code or wrapped around a selection of code you have chosen. If you choose Wrap Selection, then you will have to include an Insert Before and an Insert After code. Once you have the information filled in, Click on OK and your Snippet is ready to be used by you.

"That was easy!" But how do you get it to be used by other people? Let me begin this section by briefly informing you of our system. We are using Windows 7, Adobe CS5 with Dreamweaver 11. The path names listed below may differ for you but the concept remains the same. You will just have to locate the files for your system.

When you follow the steps to create your Snippet, Dreamweaver creates a .csn file under your computer profile located at: \Users\{User Profile}\AppData\Roaming\Adobe\Dreamweaver CS5\en_US\Configuration\Snippets. The .csn is an XML file and you can modify it directly if you are comfortable with that. You will need this file and one more file before you are ready to share your Snippets.

The other file is called an .mxi file which is another XML file. This file is an Adobe Extension Information file which the Extension Manager will use to create the extension package to install in your Dreamweaver. You can find a sample of this file at: C:\Program Files (x86)\Adobe\Adobe Extension Manager CS5\Samples\Dreamweaver\DreamweaverBlank.mxi. Open it in a text editor to view. Here is an example .mxi from our system:

<macromedia-extension
   name="Custom Snippets"
   version="1.0"
   type="suite">

   <!-- Describe the author -->
   
   <author name="Our Team" />

   <!-- List the required/compatible products -->
   
   <products>
      <product name="Dreamweaver" version="11" primary="true" />
   </products>

   <!-- Describe the extension -->
   
   <description>
   <![CDATA[A collection of custom snippets to be shared among team members.]]>
   </description>

   <!-- Describe where the extension shows in the UI of the product -->

   <ui-access>
   <![CDATA[The Snippets will be located inside the Custom Snippets folder of the Snippets Panel.]]>
   </ui-access>

   <!-- Describe the files that comprise the extension -->
   
   <files>
      <file name="cfButton100.csn" destination="$dreamweaver/configuration/Snippets/Custom_Snippets" />
      <file name="cfButton200.csn" destination="$dreamweaver/configuration/Snippets/Custom_Snippets" />
      <file name="GROCommentSection.csn" destination="$dreamweaver/configuration/Snippets/Custom_Snippets" />
      <file name="cfMessageBox.csn" destination="$dreamweaver/configuration/Snippets/Custom_Snippets" />
      <file name="cfquery.csn" destination="$dreamweaver/configuration/Snippets/Custom_Snippets" />
   </files>

   <!-- Describe the changes to the configuration -->
   
   <configuration-changes>
   </configuration-changes>

</macromedia-extension>

To modify this file, fill in the Name, Version and Type (because I was packaging several snippets, I used "suite" as the type. For a single snippet, the correct value is "codesnippet".) The rest is optional with the exception of the Files section. This is the most important section. It details a separate tag for each Snippet you want to package together for your Extension. The name attribute is the name of the .csn file for the snippet. The destination always begins with $dreamweaver/configuration/ followed by the name of the folder(s) where the files are to be installed. In the case of snippets, you should use Snippets followed by the name of any subfolder you want to store them in. The destination folders don't need to exist. The Extension Manager creates them automatically. Also, for convenience, I copied the .csn files to the same folder as my .mxi file. Otherwise the name attribute for each .csn file needs to include the path to the file's current location.

Now you are ready to create the Extension package. Open the Extension Manager and select File - Package MXP Extension. Choose the .mxi file and then name your .mxp file (this is the packaged Extension file type). Now you can install the Snippet on any computer. Just open the Extension manager and choose Install. Select the .mxp file you want to install and your Snippets will be in the Snippet panel.

If you are on a network with multiple team members, recommend you create a folder to store all your .csn, .mxi and .mxp files together. This way all team members will have access to all the information they need.

Last year, I wrote a File Management application in ColdFusion9. It allows users to manage their uploaded content online through a web Graphical User Interface (GUI). Sometimes, when a user is moving a folder of files, or renaming a folder of files; the SQL will not execute the as expected. This seems to happen when the files are held open by another user.

As a result, the database does not update with the new information, but the ColdFusion manipulates the files and folders anyway. This causes the application to become unstable since it now has rouge files and folders that are not tracked by the database.

To help reduce this risk, I recently implemented a small level of error checking on the SQL statements using @@ROWCOUNT. This has only been tested with MS SQL2008 and ColdFusion9.

<cfquery name="SetFileValue" datasource="DataBaseName">
   SET NOCOUNT ON
   UPDATE   TableFile
   SET   FileValue = 'SomeValue'
   WHERE   FileID = 2

   SELECT ChangeCount = @@ROWCOUNT
   SET NOCOUNT OFF
</cfquery>

This will result in a value being assigned to SetFileValue.ChangeCount and I can check if it is greater than zero, meaning more that one result has occurred in the update query. I could also check with a SetFileValue.RecordCount, but this is making SQL do more work and returning a scoped value instead of a ColdFusion query result value. If you follow. The @@ROWCOUNT method above was aslo used because it can be retrieved now by MS SQL, not just ColdFusion.

Now I can check that the update actually occurred and resulted in at least one record change before performing the next step. In this case, renaming a file from one value to another on the server.

<cfif IsDefined("SetFileValue.ChangeCount") AND SetFileValue.ChangeCount GT 0>
   <cffile action="rename" destination="#FileNew#" source="#FileOld#">
</cfif>

If you find this post useful please leave a comment and let me know how you used the information.

There are usually many different ways to obtain the same results. Not to mention the differences between several right ways and several wrong ways, but that's a post for another day. Consider the following query:

SELECT Record

FROM Table

WHERE RecordID = 1 OR

RecordID = 2

ﾠ

Results:

RECORD
----------
This
That

The table records a RecordID of 1 and 2 for two records named Record (bad names used for an example only) which contain this and that. When queried the results return two records. If you need to return these records in a special order, or parent/child association (RecordID 1 belongs to RecordID 2) you could also consider the following query which will name the two results as two different fields in one horizontal query row.

SELECT SubTable.Record AS SubRecord, Table.Record AS Record

FROM Table AS SubTable INNER JOIN

Table ON SubTable.RecordID = 1 AND Table.RecordID = 2

ﾠ

Results:

SUBRECORD RECORD
---------------------------
Thisﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠ That

If you find this post useful please leave a comment and let me know how you used the information