How comma separated values are divided into rows?

  • Hi Experts,

    I have the input:

    rankcountrycity

    1indiahyderabad, bangalore, delhi, shimla

    2usanewjersey, newyork, washington, texas

    3uklondon, 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

  • You need to use the Unpivot transformation.

    How To Use the Unpivot Data Flow Transform in SQL Server Integration Services SSIS[/url]

    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.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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:

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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

  • 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)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (2/6/2014)

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

    QOTD probably.

    _______________________________________________________________

    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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Since "I have the input:" doesn't specify where it's coming from, your T-SQL solutions seem to assume that the data is coming from a SQL database. If not, there are other required steps to insert it into a database, transform it, and clean up afterward.

    If this data is coming from outside SQL, say a text file, and your goal is to transform it in SSIS before further processing and/or database insertion, a script transform component might be the best answer. Parse the first two fixed columns of each input row, then use String.Split on the city list and an AddRow method in a loop over the results.

    I like T-SQL as much as the next guy, but when someone is talking about transforming data in SSIS I don't assume T-SQL is the only answer.

    PS I'm the guy with a couple of SSIS packages that have evolved into one script task labeled "Do everything". I think they're perfectly maintainable, but nobody else around here has actually tried it.

  • I like T-SQL as much as the next guy, but when someone is talking about transforming data in SSIS I don't assume T-SQL is the only answer.

    You seem to be implying that we do, is that correct?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I was just making the observation that the original post did not say where the data was coming from or where it was going. However clever the T-SQL solutions are, I saw no mention of the possibility of any other method. There were no questions about the data source, destination, or number of rows.

    Usually in these forums I see a lot of enlightened discourse about possible gotchas or other aspects of a problem that would never occur to me, and find it very informative. This time I felt the proposed solutions may not have had enough variety. I doubt that most of you would have any trouble implementing the script transform I described, it's not a shockingly clever idea. It just seemed to have been overlooked.

  • Scott Coleman (2/6/2014)


    I was just making the observation that the original post did not say where the data was coming from or where it was going. However clever the T-SQL solutions are, I saw no mention of the possibility of any other method. There were no questions about the data source, destination, or number of rows.

    Usually in these forums I see a lot of enlightened discourse about possible gotchas or other aspects of a problem that would never occur to me, and find it very informative. This time I felt the proposed solutions may not have had enough variety. I doubt that most of you would have any trouble implementing the script transform I described, it's not a shockingly clever idea. It just seemed to have been overlooked.

    Even though the very first response contains this?

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

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply