Topic : Conversion Of comma separated value into a Table Valued function (Splitting up of comma separated Values).

This post actually helps for the developers when they have a requirement such as, the data coming in with a pipe separated value or a comma separated value, and these values has to converted into a table data . So especially whenever their is a requirement for conversion of string data into a table then any of the below logic helps in this conversion

There are two ways where u can achieve this functionality.

1. Can be achieved by creating a Table Valued function

2. Can be achieved by XML functions.

Type-1 : Creation of Function for Split

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Split]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[Split]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [DBO].[SPLIT](@INPSTRNG NVARCHAR(MAX), @DELIMITER CHAR(1))        

RETURNS @TABLEVALUED TABLE (SPLITTEDVALUES NVARCHAR(40))        

AS        

BEGIN        

    DECLARE @STARTER INT        

    DECLARE @DATA NVARCHAR(MAX)        

       

    SELECT @STARTER = 1        

        IF LEN(@INPSTRNG)<1 OR @INPSTRNG IS NULL  RETURN        

       

    WHILE @STARTER!= 0        

    BEGIN        

        SET @STARTER = CHARINDEX(@DELIMITER,@INPSTRNG)        

        IF @STARTER!=0        

            SET @DATA = LEFT(@INPSTRNG,@STARTER - 1)        

        ELSE        

            SET @DATA = @INPSTRNG        

           

        IF(LEN(@DATA)>0)   

            INSERT INTO @TABLEVALUED(SPLITTEDVALUES) VALUES(@DATA)        

  

        SET @INPSTRNG = RIGHT(@INPSTRNG,LEN(@INPSTRNG) - @STARTER)        

        IF LEN(@INPSTRNG) = 0 BREAK        

    END    

RETURN        

END

EXAMPLE:

1.SELECT * FROM [DBO].[SPLIT]('A,B,C,D,E,F',',')

2.SELECT * FROM [DBO].[SPLIT]('11|22|33|44|55|66|77','|')

Type-2 : Splitting up of Comma separated data using XML functions.

Sometimes if there are any constraints for creating a function for splitting up of data then here is another way where we can achieve it using XML functions.

EXAMPLE: 1

Using single Value

Declare @CommSepVal VARCHAR(500)='AA,BB,CC,DD,FF,GG,HH,LL',

@Xml  Xml

Set  @Xml = N'<root><r>' + replace(@CommSepVal,',','</r><r>') + '</r></root>'

Select * 

  From

(

Select Val.value('.','varchar(150)') As Data

 From @Xml.nodes('//root/r') As A(Val)

) Fin

EXAMPLE: 2

Table Values with Commaseparated Values

DECLARE @TABLE TABLE

(

ID INT,

VLUES VARCHAR(100)

)

INSERT @TABLE

VALUES (1,'AA,BB,CC,DD'),(2,'FF,GG,HH,LL'),(3,'W,E')

SELECT ID,DATA FROM (SELECT ID,CONVERT(XML,N'<ROOT><R>' + REPLACE(VLUES,',','</R><R>') + '</R></ROOT>') AS VALUE FROM @TABLE) AS A

CROSS APPLY (

SELECT * 

  FROM

(

SELECT Val.value('.','VARCHAR(150)') AS DATA

 FROM A.value.nodes('//ROOT/R') AS A(Val)

) FIN ) AS B

SSIS - Loading data From Different Excel File and form different sheets dynamically.

Very often i have seen people asking question like is their a way where i can load data from different sheets of an excel dynamically Or can i load data from different excel files dynamically.I will say yes u can definetly do that. This post tells about loading data from different Excel sheets Dynamically and also getting data from different sheets of excel file dynamically.

Following are the steps explained in detail .

Step1:
Create a Excel File Connection by pointing to the directory where the excel files will be stored.

Select a proper path by browsing it to any Excel File and click OK.

Step2 :

Declare A Variables at Package Level One for Path and Other For File Name.
For Example: PathName,FileName
Here in my example i have hard coded the value for PathName i.e.
PathName = 'D:\Paper_Daddy\Different Centers\'
And FileName Initially give a value of any excel sheet located in above path.

Step3: 
Use a For Each Loop Container to get the file names dynamically.

Connections and Properties of For Each Loop container is as shown below.

Variable Mapping For Each Loop container is as shown below.

So above Step3 ll fetch the the file names dynamically from the path mentioned in the variable.

Next set of steps is for getting the sheet names of each excel file dynamically.

Step4:

Use a For Each loop Container for getting sheet names dynamically.Declare one more variable to store work sheet names say "WorkSheetName" and provide any one of the  sheet name of File mentioned in above file Name variable (In step2).

This For Each loop container must be inside the container created in Step-1.Add a Data Flow task inside the second For Each Loop Container.

Below are the Connections for For Each loop container that extracts worksheet names of each excel sheets.

 
In above screen shot, the Connection string can be any of the excel file.Double click on the above connection string and Select all tab and change the settings as shown below

The variable "WorkSheetName" must be mapped with the settings as below.

The variable WorkSheetName which holds the value of wrksheetnames of different excel sheets cabn either be inserted into a table in database or can also be used with in the Excel source connection inside Dataflow task.

Thus this post helps in extracting the wrksheetnames of dynamic excel sheets . 

SQL Server Common Table Expression:

Many developers often find difficult to write a complex query using Joins.Many Complex queries written using sub queries or joins can be simplified by using a technique called Common Table Expression Often called as CTE. I ll try to cover most of the details related to CTE in this post.

Most of the time, a sub query which fetches a set of data will have to be used at many instances in a one single query and hence writing it by using CTE will not only improves the performance but also the ease of writing SQL's.

CTE is standard ANSI SQL standard.CTE are oftentimes used during recursive process in which the query executes itself. CTE can also be called as a tremporary table and can be referenced within Select,Insert,Update and Also Delete statements.

CTE are of two types

• 1>  Recursive CTE
• 2> Non Recursive CTE

CTE are constructed with three core parts 

•  CTE Name (This is always followed by a WITH clause/Keyword)-Mandatory
• Column Names or the List of columns - Optional
• Query (This appears with a small parenthesis after the keyword AS)-Mandatory

Common Table Expression Or the CTE are represented as below

Advantages Of CTE:

• Referred as a table multiple times within a query.
• Mainly used in building recursive queries.
• Readability is increased.
• Can be referred as view
• CTE helps in dividing a query into simpler blocks or chunks of data and these chunks can be used as a building blocks for complex queries.

Examples For Recursive and Non recursive CTE's

---Non Recurssive CTE

WITH Vendors(V_Id, V_Sales)

as

(

  SELECT S.SalesPersonID, COUNT(*)

  FROM Adventureworks.Sales.SalesOrderHeader S

  WHERE S.SalesPersonID IS NOT NULL

  GROUP BY S.SalesPersonID

)

SELECT V.FirstName, Cte.V_Sales

FROM Adventureworks.Sales.vSalesPerson V

INNER JOIN Vendors Cte ON V.SalesPersonID=Cte.V_Id

ORDER BY Cte.V_Sales

-----Example For Normal CTE

IF OBJECT_ID('RecurssiveCte', 'U') IS NOT NULL

DROP TABLE dbo.RecurssiveCte

GO

CREATE TABLE dbo.RecurssiveCte

(

  Id int NOT NULL PRIMARY KEY,

  FirstName varchar(50) NOT NULL,

  SurName varchar(50) NOT NULL,

  MgrID int NULL

)

GO

INSERT INTO RecurssiveCte VALUES (101, 'Kiran', 'Kashyap', NULL)

INSERT INTO RecurssiveCte VALUES (102, 'Lalitha', 'Kashyap', 101)

INSERT INTO RecurssiveCte VALUES (103, 'Suhas', 'Kashyap', 101)

INSERT INTO RecurssiveCte VALUES (104, 'Gayathri', 'Kashyap', 102)

INSERT INTO RecurssiveCte VALUES (105, 'Guru', 'Darshan', 102)

INSERT INTO RecurssiveCte VALUES (106, 'Vidya', 'Kashyap', 103)

INSERT INTO RecurssiveCte VALUES (107, 'Rajesh', 'Kashyap', 103)

INSERT INTO RecurssiveCte VALUES (108, 'Shruthi', 'Kashyap', 105)

INSERT INTO RecurssiveCte VALUES (109, 'Sujay', 'Kashyap', 105)

INSERT INTO RecurssiveCte VALUES (110, 'Vijay', 'Kashyap', 106);

;WITH

  RecursiveCTE (Id, FirstName, SurName, MgrID, Lvl)

  AS

  (

    SELECT Id, FirstName, SurName, MgrID, 1 Lvl

    FROM RecurssiveCte

    WHERE MgrID IS NULL

    UNION ALL

    SELECT e.Id, e.FirstName, e.SurName, e.MgrID,r.Lvl + 1

    FROM RecurssiveCte e

    INNER JOIN RecursiveCTE r ON e.MgrID = r.Id

  )

SELECT FirstName + ' ' + SurName AS FullName,Lvl

FROM RecursiveCTE

ORDER BY Lvl, MgrID

I was going thr a particular requirement on MSDN forums today and i have shared the same here which could actually help in understanding CTE more


Declare @Budget Table
(
Budget_Revenue Int,
Budget_Traffic Int,
Actual_Revenue Int ,
Actual_Traffic Int
)
Insert @Budget Values
(0, 0, 2347585, 295),
(0, 0, 8796, 6),
(0, 0, 107772, 18),
(0, 0, 153940, 10),
(0, 0, 284510, 13),
(3772, 1, 2347585, 295),
(3772, 1, 8796, 6),
(3772, 1, 107772, 18),
(3772, 1, 153940, 10),
(3772, 1, 284510, 13),
(143355, 19, 2347585, 295),
(143355, 19, 8796, 6),
(143355, 19, 107772, 18),
(143355, 19, 153940, 10),
(143355, 19, 284510, 13),
(7545, 1, 2347585, 295),
(7545, 1, 8796 ,6),
(7545, 1, 107772 ,18),
(7545, 1, 153940 ,10),
(7545, 1, 284510 ,13),
(0, NULL, 2347585,295),
(0, NULL, 8796 ,6),
(0, NULL, 107772 ,18),
(0, NULL, 153940 ,10),
(0, NULL, 284510 ,13)

;With Cte As
(
Select Dense_Rank() Over (Order By (Select Case When ForSort = 295 then 0 Else ForSort End))Rnk,*
From (
Select T1.Budget_Revenue,T1.Budget_Traffic,Stuff(
(Select ','+ Convert(Varchar(10),T2.Actual_Revenue)
From @Budget T2
  Where T1.Budget_Revenue = T2.Budget_Revenue
And IsNull(T1.Budget_Traffic,1010) = IsNull(T2.Budget_Traffic,1010)
For Xml Path('')
),1,1,'') As Actual_Revenue,
 
Stuff(
(Select ','+ Convert(Varchar(10),T2.Actual_Traffic)
  From @Budget T2
     Where T1.Budget_Revenue = T2.Budget_Revenue
   And IsNull(T1.Budget_Traffic,1010) = IsNull(T2.Budget_Traffic,1010)
  For Xml Path('')
),1,1,'') As Actual_Traffic,T1.Actual_Traffic ForSort

From @Budget t1
) T1

),Budget_New As(Select Row_Number() Over (Order By (Select Null))RwID,Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic From Cte
Where Rnk=1
)
, Cte1 As
(
Select RwId,
       Budget_Revenue
       ,Budget_Traffic
       ,Actual_Revenue + ','Actual_RevenueCm
       ,Actual_Traffic + ',' Actual_TrafficCm
       ,Substring(Actual_Revenue,1,CharIndex(',',Actual_Revenue)-1) As Actual_Revenue
       ,Substring(Actual_Traffic,1,CharIndex(',',Actual_Traffic)-1) As Actual_Traffic
       ,CharIndex(',',Actual_Revenue) As ForActual_Revenue
       ,CharIndex(',',Actual_Traffic) As ForActual_Traffic
  From Budget_New
Where RwId = 1
Union All
Select
T1.RwId,
T1.Budget_Revenue
,T1.Budget_Traffic
,Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue))
        ,Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic))
        ,Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,CharIndex(',',Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue)))-1)
        ,Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,CharIndex(',',Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic)))-1)
        ,CharIndex(',',Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue)) ) As ForActual_Revenue
        ,CharIndex(',',Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic)) ) As ForActual_Traffic
From Budget_New T1
Join Cte1 T2 On T1.RwId = T2.RwId+1
)
Select Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic From Cte1