How to split a comma-separated value to rows

Question

I have .csv file have two columns

 Main Category               Sub Category
--------------------------------------------------------                    
Technology/Internet    Computers/Internet,Technology/Internet,Software Downloads, Social media

I'd like to make these two columns like that

 Main Category               Sub Category
--------------------------------------------------------                    
Technology/Internet    Computers/Internet
Technology/Internet    Technology/Internet                  
Technology/Internet    Software Downloads
Technology/Internet    Social media

I tried to use this expression: SUBSTRING(Sub Category,1,FINDSTRING(Sub Category,"-",1) + 1,LEN(Sub Category)) in derived column in SSIS but it dose not work and, can I put while loop like that in SSIS

Declare 
@start int,
@end int,
@category_group Varchar(250),
@sub_category varchar(250)

set @start = 1
set @end = 99
set @category_group =(select distinct([Sub_Category]) from [Site_Categories])
set @sub_category = 
(select replace(substring([Sub_Category],1,charindex(N',',[Sub_Category],1)),',','') from  [Site_Categories])

while 
@start <= @end
begin
insert into [dbo].[Category_group]
([category_group],[sub_category])
select 
@category_group,
@sub_category

set @start= @start+1
end

please what I should do to fix this Case in SSIS

Answer 1

It seems that you have two problems: 1) Split the column subcategory and 2) Convert 1 row into 2 rows.

problem 1 can be solved this way:

left part of the string:

SUBSTRING(Sub Category,1,FINDSTRING(Sub Category,",",1) - 1)

right part of the string:

SUBSTRING(Sub Category,FINDSTRING(Sub Category,",",1) + 1,LEN(Sub Category))

problem 2 can be solved with this data flow:

Answer 2

This solution might help:Split a single column of data with comma delimiters into multiple columns in SSIS

or this link:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/67916550-62ad-45ab-a013-b3c45f133334/ssis-split-comma-separated-list-using-script-task?forum=transactsql

or here:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/386e5513-7e61-4448-a78d-bf04e568e1dc/import-file-csv-into-sql-server?forum=transactsql

Answer 3

Your Data

DECLARE @TABLE TABLE ( MainCategory  NVARCHAR(100),SubCategory NVARCHAR(100))
INSERT INTO @TABLE VALUES
('Technology/Internet' ,   'Computers/Internet, Technology/Internet'),
('Technology/Intranet' ,   'Laptops/IPads, Laptops/Tablets')

Query

SELECT T.MainCategory, Item AS SubCategory
FROM @TABLE t 
     CROSS APPLY 
       (SELECT * FROM dbo.Split(SubCategory, ','))S

Result Set

╔═════════════════════╦══════════════════════╗
║    MainCategory     ║     SubCategory      ║
╠═════════════════════╬══════════════════════╣
║ Technology/Internet ║ Computers/Internet   ║
║ Technology/Internet ║  Technology/Internet ║
║ Technology/Intranet ║ Laptops/IPads        ║
║ Technology/Intranet ║  Laptops/Tablets     ║
╚═════════════════════╩══════════════════════╝

Split Function

CREATE FUNCTION Split (  
      @InputString                  VARCHAR(8000),  
      @Delimiter                    VARCHAR(50)  
)  

RETURNS @Items TABLE (  
      Item                          VARCHAR(8000)  
)  

AS  
BEGIN  
      IF @Delimiter = ' '  
      BEGIN  
            SET @Delimiter = ','  
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)  
      END  

      IF (@Delimiter IS NULL OR @Delimiter = '')  
            SET @Delimiter = ','  

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic  
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic  

      DECLARE @Item                 VARCHAR(8000)  
      DECLARE @ItemList       VARCHAR(8000)  
      DECLARE @DelimIndex     INT  

      SET @ItemList = @InputString  
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)  
      WHILE (@DelimIndex != 0)  
      BEGIN  
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)  
            INSERT INTO @Items VALUES (@Item)  

            -- Set @ItemList = @ItemList minus one less item  
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)  
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)  
      END -- End WHILE  

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString  
      BEGIN  
            SET @Item = @ItemList  
            INSERT INTO @Items VALUES (@Item)  
      END  

      -- No delimiters were encountered in @InputString, so just return @InputString  
      ELSE INSERT INTO @Items VALUES (@InputString)  

      RETURN  

END -- End Function