SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How comma separated values are divided into rows?


How comma separated values are divided into rows?

Author
Message
vijay.sap1255
vijay.sap1255
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 62
Hi Experts,

I have the input:
rank country city
1 india hyderabad, bangalore, delhi, shimla
2 usa newjersey, newyork, washington, texas
3 uk london, greenland, denmark, italy, spain

expected output:

rank country city
1 india hyderabad
1 india banalore
1 india delhi
1 india shimla similarly for rank 2 and 3


Can anyone help me how to achieve this in SSIS BIDS. What logic should be used and in which componenet.??

Thank you in Advance,
Regards,
Vijayvanamala
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63680 Visits: 13298
You need to use the Unpivot transformation.

How To Use the Unpivot Data Flow Transform in SQL Server Integration Services SSIS

The unpivot however accepts only a fixed number of columns as input though. So you have to make sure the city column is splitted into multiple columns and that this number is fixed.

If this is not possible, you'll have to implement it with a script component.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218751 Visits: 41998
I wouldn't do this part in SSIS. Post some readily consumable data according to the first link in my signature line below under "Helpful Links" and if I don't get to it, I'm sure that someone else will.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63771 Visits: 17974
I am pretty sure that Jeff was thinking of using the DelimitedSplit8K function for this.

I create the ddl and sample data for you to demonstrate how you should post this in the future.


create table #Something
(
CityRank int,
Country varchar(10),
City varchar(200)
)

insert #Something
select 1, 'india', 'hyderabad, bangalore, delhi, shimla' union all
select 2, 'usa', 'newjersey, newyork, washington, texas' union all
select 3, 'uk', 'london, greenland, denmark, italy, spain'

select CityRank, Country, ltrim(x.Item)
from #Something s
cross apply dbo.DelimitedSplit8K(s.City, ',') x

drop table #Something



You will need to read the article in my signature about splitting strings. In there you will find how to make the DelimitedSplit8K function on your server. Make sure you read and understand the technique here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218751 Visits: 41998
Sean Lange (2/5/2014)
I am pretty sure that Jeff was thinking of using the DelimitedSplit8K function for this.


Correct and well done. Thanks for the cover, Sean.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63771 Visits: 17974
Jeff Moden (2/5/2014)
Sean Lange (2/5/2014)
I am pretty sure that Jeff was thinking of using the DelimitedSplit8K function for this.


Correct and well done. Thanks for the cover, Sean.


My pleasure.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52656 Visits: 21194
I tried the UNPIVOT transformation for this problem and after pratting about with it for a while in an attempt to make it work, I have come down soundly on the side of Jeff and Sean - do this outside of SSIS.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63680 Visits: 13298
Phil Parkin (2/5/2014)
I tried the UNPIVOT transformation for this problem and after pratting about with it for a while in an attempt to make it work, I have come down soundly on the side of Jeff and Sean - do this outside of SSIS.


And if you think the UNPIVOT has an unholy GUI, try the PIVOT one for a change...

Hehe


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
SQLCJ
SQLCJ
Say Hey Kid
Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)Say Hey Kid (695 reputation)

Group: General Forum Members
Points: 695 Visits: 576
Would it be ok for you to use recursive CTE ? ..something like

create table #Something
(
CityRank int,
Country varchar(10),
City varchar(200)
)

insert #Something
select 1, 'india', 'hyderabad, bangalore, delhi, shimla' union all
select 2, 'usa', 'newjersey, newyork, washington, texas' union all
select 3, 'uk', 'london, greenland, denmark, italy, spain'

--SELECT * FROM #Something

;WITH CTE AS
(
SELECT CityRank,Country,City,SUBSTRING(CITY,1,CHARINDEX(',',CITY,1)-1) AS C_CITY,
SUBSTRING(CITY,CHARINDEX(',',CITY,1)+1,LEN(CITY)) AS C_STR FROM #Something
UNION ALL
SELECT CityRank,Country,City,LTRIM(RTRIM(SUBSTRING(C_STR,1,CASE
WHEN CHARINDEX(',',C_STR,1) = 0 THEN LEN(C_STR)
ELSE CHARINDEX(',',C_STR,1) - 1
END ))),
SUBSTRING(C_STR,CASE
WHEN CHARINDEX(',',C_STR,1) = 0 THEN NULL
ELSE CHARINDEX(',',C_STR,1)+1
END,CASE
WHEN LEN(C_STR) = 0 THEN 1
ELSE LEN(C_STR)
END) FROM CTE
WHERE CHARINDEX(',',C_STR,1) >= 0 --OR
)
SELECT CityRank,Country,C_CITY AS CITY FROM CTE ORDER BY CityRank

I'm sorry for the formatting/colors ..as this is my first post and do not know how to post with SQL formatting being intact
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52656 Visits: 21194
chetan.deshpande001 (2/6/2014)
Would it be ok for you to use recursive CTE ? ..something like

--Edit

I'm sorry for the formatting/colors ..as this is my first post and do not know how to post with SQL formatting being intact



OK ... perhaps, but the proposed Delimited8KSplit solution will outperform it, I'd bet my wife's shoes on that.

Out of interest, how did you get 329 points here without posting anything?

T-SQL formatting: All you need to do is enclose your code with {code="sql"}{/code} tags (use square brackets)


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search