SQL server Multiple rows to one comma separated value

This is the select statement in Sql server which will select the multiple rows of a column into a single column and single row as a comma separated values.

SELECT  ID ,STUFF((SELECT ‘, ‘ + CAST(Name AS VARCHAR(10)) [text()] FROM Customer WHERE GroupId = t.ID FOR XML PATH(”), TYPE).value(‘.’,’NVARCHAR(MAX)’),1,2,’ ‘) List_Output
FROM CustomerGroup t
GROUP BY ID

You can test the functionality here http://sqlfiddle.com/#!6/1cda0e/4

Advertisements

Sql Sever Split String function with special character support

The following the is the SQL server table valued function which will return the result as table. The input to the function is string delimited with the comma (for that matter any character which is is not part of the string it self) and the delimited character. This will also work even if the special characters present in the given input string.

1. Function

Create FUNCTION UdfSplitString
(
   @String       NVARCHAR(MAX),
   @SplitChar  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value(‘(./text())[1]’, ‘nvarchar(4000)’)
      FROM 
      ( 
        SELECT x = CONVERT(XML, ‘<![CDATA[‘ 
          + REPLACE(@String, @SplitChar, ‘]]><![CDATA[‘) 
          + ‘]]>‘).query(‘.’)
      ) AS a CROSS APPLY x.nodes(‘i’) AS y(i)
   );
GO


2. Select Statement

Select * from UdfSplitString(‘1,2,3,*@#$~!%^&*()-+=_{}|’,’,’)

You can also test the functionality from the following link. http://sqlfiddle.com/#!6/e667f/3/0

Chain of Responsibility design pattern with Dynamic handlers

This is the Chain of responsibility pattern with the dynamic handler. With this, we can assign a dynamic handler for the request without touching the main handler.

The following is the image shows the list of projects I have used in the demo project.
1. PatternTest –> is the actual client which will use the design pattern
2. Core –> is the Main project which will have the default implementation
3. AddOn –> this is the first add-on which will extend the core functionality like that we have AddOnTwo and AddOnThree project.
1. PatternTest
In this Project, we have two important files ManageHandlers.cs and Handler.xml. These two files provide the dynamic behavior.

  • ManageHandler.cs: This file contains the code that will load the handlers for the main request. This will take the handler details from Handler.xml file
  • Handler.xml: This file contains the details about dynamic handlers. Assembly path name and class name (Including the namespace).
2. Core
In this Project, we have the main business logic. It will deal with the required logic for default implementation for all the possible scenario.
3. AddOn One, Two and Three
These projects deal with the specific scenario. These will extend the core functionality and provide the additional functionality. The execution of these add-ons will be controlled by the Core module itself using the Handler.xml file.Link to the Sample Project is here Download