How to Create a Comma-Delimited Result set Using FOR XML PATH()

There has been many times that I have a result set that has multiple rows for one given column and I want to have all those results in one row; effectively reducing the output.

In this case we have an output of groups, users, and codes, respectively.  The preferred output would be one row per user code.  But as you can see there are several codes per user.

This is where the FOR XML PATH('') and STUFF() come into play.  How do we do this?

Here is the syntax for what we want to accomplish and then we'll go through it a little more detailed.

Stuff syntax:
STUFF( select statement, start index, length, character expression )

For XML Path syntax: 
FOR XML PATH(element)  in our case we want to put two single quotes in the for xml path parentheses.

FOR XML PATH('')
To get the data into the comma delimited result we start with FOR XML PATH(''):

Note that if you want the XML out you would not use ('') at the end of FOR XML PATH.  If you omit this then you will get an out put for that column like

<data ><row>, <row><data ..."

By adding the ('') you remove the XML wrapper elements.  You can read more on MSDN Books Online under FOR XML PATH.










STUFF()

Notice that we started the string with a comma.  You could end it with a comma if you choose to just changes how  the next phase works.  I choose to put it at the beginning and then trim it with STUFF().

Which brings us to cleanup by using STUFF().  By using stuff we can omit the leading comma from the result set giving us a cleaner output in case you want to parse it later. STUFF() is like using a range REPLACE().  Instead of replacing all instances of something we are only replacing a specified location or range of characters.



Lets look at the syntax and break it down:

• Syntax: STUFF(, , , )
• start index tells us where to begin replacing characters with character string.
• length tells us how many characters are we going to be affecting.
• character string tells us what we are going to be replacing or "stuffing" in place of the affected characters.  In our case we replaced only the leading comma giving us the follow result.

 NOTE:You only need to use STUFF() if you want to trim the result.  If you are satisfied with your result then you don't need to use this.  I am only using it to trim the leading comma.

AutoNumber Group Sections within a Group Record: Crystal Reports

         

I had an interesting situation the other day when attempting to consolidate redundant .rpt files. The only difference in these files was the inclusion of some paragraphs, sentences, or words within a given header. As we all know I do consulting and IT work for the Real Estate industry and a desire to combine lease agreements and not have an agreement for each state - making editing that reflected multiple states become an editing pain and nightmare - for ease in editing and maintenance/management. But with that each header has a roman numeral with the header, such as "III. RENT". When combining multiple states some sections may apply to one state and not another. For example, Utah, Arizona, etc allow for a lease initiation fee whereas California does not. With that in mind the "IV. LEASE INITIATION FEE" section would be suppressed for California and not for other states. Also the record set returned to the report was a single record - making auto incrementing a chore.
So let's address each issue.
We need to make a couple of assumptions first:

Only 1 record

Each paragraph in the agreement is its own Group Header (gh1a, gh1b, gh1c...)

Only Sections showing are Group Headers and Page Footer

Numbering should be consistent, regardless of suppressed sections

1 - Getting the report to auto increment and only on the headers that we want.

The problem here is that there really isn't anything to base a running total or change on N group/record. Adding a function to increment a variable won't work either as the function will only run once no matter how many times you place it into the report file. So what can we do? We need a function that will increment as many times as we need during the compiling pass through. We want to also use a simple function right? What about something like this:

    WhilePrintingRecords; //Used for convenience of using either Shared or Global Variables
    Shared NumberVar X:=X+1;

Sounds simple and should work for what we want - simply increment X for every header we place the function.  So I placed the function on each header that I wanted to increment and found that the function incremented for the first placement and not the remainder.  FRUSTRATING!!!

What I ended up having to do for single pass through report like this was create a function with the above code for every header and place at each header.  So header 1. would have
@1 - HEADER, next header @2 - HEADER, etc.

2 - What about identical header sections that have suppression based on some variable?

Remember though that we have some section that may vary slightly for each state but are in every agreement such as RENT.  In my consolidation I have 3 different RENT, LATE CHARGES, etc  sections.  How do I make sure the correct one is showing with the right number?  This was the simple part.  When you have multiple section that would have the same number you simply use the same function.  Therefore, when the report runs, the number would be the same for either section and would not get incremented more than once.

3 - But what about sections that are suppressed altogether? 
 This had me for a few minutes.  What I was seeing with the supplied function, when a section was suppressed outright - not because of multiple options as we discussed in the previous section - the function was still firing and incrementing X, resulting in a gap in the numbering.

To overcome this we need to overcome the suppression formula by adding the suppress formula to the increment formula. If the suppress formula is true then do not increment, otherwise do increment.

    WhilePrintingRecords;
    Shared NumberVar X;
    IF {field} = {value} //Suppress expression
    THEN X:=X
    ELSE X:=X+1;



Once all this was in place for each section that had some kind of suppression - again exclude for multiple option suppression - the numbering worked perfectly, allowing for multiple section options and suppressed sections.

Where it is a legal document it is common practice to use Roman Numerals when numbering sections.  To do that you simply use ROMAN().
Note: The use of ROMAN() changes a numeric value to a string value. 

My final coding(s) look like the following:

//EACH HEADER I DESIRE NUMBERING
    WhilePrintingRecords;
    Shared NumberVar X;
    Roman(X:=X+1);

//EACH HEADER THAT IS BEING SUPPRESSED BUT NOT PART OF A MULTIPLE OPTION

    WhilePrintingRecords;
    Shared NumberVar X;

    IF {field} = {value}
    THEN Roman(X)
    ELSE Roman(X:=X+1);


Please like this if you found it useful.