none
Split a row into multiple rows on multiple columns RRS feed

  • Question

  • I want to single rows in multiple value columns to rows. there are more than 90 columns in the table. some of them have multiple values divided by'&$&', some columns are not.

    for example

    customerid    field1                                   field2 .....  field 99

    1                   English &$& French

    2                                                              d&$&f                 1&$&b

    -->

    customer id   field1                   field2 ......... field99

    1                   English

    1                   French

    2                                            d                    1

    2                                            d                     b

    2                                            f                      1

    2                                            f                       b

    How can I accomplish this?? Thank you so much!!

    Thursday, April 18, 2013 6:08 PM

Answers

  • You could also use the VB.net script instead of C# :-)

    A simple solution for multiple columns is to add two Script Components. First for one column and then one more for the second column.

    For a more a more sophisticated solution you need to rewrite the code. Perhaps two nested while loops. Or if you want to check all columns for &%& then you could use reflection. But if you have no .Net experience at all then you have to use an other solution or ask a .net developer to help you.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by Eileen Zhao Thursday, April 25, 2013 5:58 AM
    Thursday, April 18, 2013 7:04 PM
    Moderator
  • Hi,

    Try like this code example,

    CREATE TABLE [dbo].[#Customer](
    	[username] [varchar](50) NOT NULL,
    	[report_group] [varchar](80) NOT NULL)
    	
    INSERT [dbo].[#Customer] 
    ( [username], [report_group]) VALUES ( N'User1',  N'2,3')
    INSERT [dbo].[#Customer] 
    ( [username], [report_group]) VALUES ( N'User2',  N'4,5')
    SELECT * FROM #Customer
    ;WITH CTE1
    AS
    (
    SELECT UserName,CAST(('<r><c>' + REPLACE(report_group, ',', '</c></r><r><c>') + '</c></r>' ) AS XML)Grp
    FROM [dbo].#Customer
    ),
    CTE2
    AS
    (
    SELECT UserName, CAST(Col.query('./text()') AS VARCHAR(10))Grp
    FROM 
    CTE1 CROSS APPLY 
    Grp.nodes('/r/c') As Tab(Col)
    )
    SELECT * FROM CTE2
    --DROP TABLE #Customer


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    • Marked as answer by Eileen Zhao Thursday, April 25, 2013 5:58 AM
    Friday, April 19, 2013 3:24 AM
    Moderator

All replies

  • Here is an option with a Script Component: http://microsoft-ssis.blogspot.com/2012/11/split-multi-value-column-into-multiple.html


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, April 18, 2013 6:26 PM
    Moderator
  • Thank you so much for your post. that seems only split on one column. But i have several columns.  I think i need to loop or dynamically get the column which contains the value has &%&. I am not so familiar with C#. And ideas that i can implement this method to more columns?

    Thank you so much!

    Thursday, April 18, 2013 6:48 PM
  • You could also use the VB.net script instead of C# :-)

    A simple solution for multiple columns is to add two Script Components. First for one column and then one more for the second column.

    For a more a more sophisticated solution you need to rewrite the code. Perhaps two nested while loops. Or if you want to check all columns for &%& then you could use reflection. But if you have no .Net experience at all then you have to use an other solution or ask a .net developer to help you.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by Eileen Zhao Thursday, April 25, 2013 5:58 AM
    Thursday, April 18, 2013 7:04 PM
    Moderator
  • Hi,

    Try like this code example,

    CREATE TABLE [dbo].[#Customer](
    	[username] [varchar](50) NOT NULL,
    	[report_group] [varchar](80) NOT NULL)
    	
    INSERT [dbo].[#Customer] 
    ( [username], [report_group]) VALUES ( N'User1',  N'2,3')
    INSERT [dbo].[#Customer] 
    ( [username], [report_group]) VALUES ( N'User2',  N'4,5')
    SELECT * FROM #Customer
    ;WITH CTE1
    AS
    (
    SELECT UserName,CAST(('<r><c>' + REPLACE(report_group, ',', '</c></r><r><c>') + '</c></r>' ) AS XML)Grp
    FROM [dbo].#Customer
    ),
    CTE2
    AS
    (
    SELECT UserName, CAST(Col.query('./text()') AS VARCHAR(10))Grp
    FROM 
    CTE1 CROSS APPLY 
    Grp.nodes('/r/c') As Tab(Col)
    )
    SELECT * FROM CTE2
    --DROP TABLE #Customer


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    • Marked as answer by Eileen Zhao Thursday, April 25, 2013 5:58 AM
    Friday, April 19, 2013 3:24 AM
    Moderator