none
Unpivot and Parse Columns RRS feed

  • Question

  • Hello , 

    I have a data that is set up like this

    Group 1          Group 2         Group 3       Group 4        Group 5  ......................... Group 16

    a,b,c,d,e,f        a,b,c,d,e,f     a,b,c,d,e,f     a,b,c,d,e,f      a,b,c,d,e,f                         a,b,c,d,e,f

    The output I am looking for is 

    Group Number    Group Value             column 1       column2  column3    column 4   column 5   column 6

    Group 1             a,b,c,d,e,f                  a                    b            c               d              e               f

    Group 2             a,b,c,d,e,f                  a                     b            c              d               e               f       

    Group 3             a,b,c,d,e,f  

    Group 4            a,b,c,d,e,f  

    Group 5             a,b,c,d,e,f  

    Group 6            a,b,c,d,e,f  

    Group 16          a,b,c,d,e,f                 a                       b         c              d                e                f

    How can I achieve this output?

    Thursday, June 13, 2019 9:14 PM

All replies

  • Hi AmitSSRS,
    We can achieve the output by testing as following:
    1.Add an OLE DB Source which connects to your source
    2.Use an Unpivot transformation to re-establish the source
    3.Use Derived Column to add new column and parse column values


    Expression: SUBSTRING([Group Value],1,1)

    Best Regards,

    Mona LV


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Friday, June 14, 2019 3:31 AM
  • Thanks that helps. What if I want to parse a string when you dont know start and length. We have values like this  for each group. I dont want to hardcode the length and start. It should parse using "," as delimiter 

    SAS,0:1:1,No,278.88 GB,Online,Ok,DELL,AL13SXB30EN,X5T0A03YFHTC,DK05,
    SAS,0:1:0,No,278.88 GB,Online,Ok,DELL,AL13SXB30EN,Z5N0A08CFHSC,DK05,
    SAS,0:1:1,No,278.88 GB,Online,Ok,DELL,AL13SXB30EN,Z5N0A08BFHSC,DK05,
    SAS,0:1:0,No,278.88 GB,Online,Ok,DELL,ST300MP0005,S7K0WK5P,VT33,
    SAS,0:1:1,No,278.88 GB,Online,Ok,DELL,ST300MP0005,S7K0WK8E,VT33,
    SAS,0:1:0,No,278.88 GB,Online,Ok,DELL,AL13SXB30EN,Z5N0A0FFFHSC,DK05,
    • Edited by AmitSSRS Friday, June 14, 2019 2:21 PM
    Friday, June 14, 2019 2:21 PM
  • Hi,

    Is your string always delimited by six values (a-f) or is it variable ie it could be more/less than six? Explain your data for example if Group1 value is fixed (example 5 values) but different from Group2 but it is also fixed (example 7 values), that also helps

    Saturday, June 15, 2019 9:51 AM
  • here is the sample of data:

    SAS,0:1:1,No,278.88 GB,Online,Ok,DELL,AL13SXB30EN,X5T0A03YFHTC,DK05,
    SAS,0:1:0,No,278.88 GB,Online,Ok,DELL,AL13SXB30EN,Z5N0A08CFHSC,DK05,
    SAS,0:1:1,No,278.88 GB,Online,Ok,DELL,AL13SXB30EN,Z5N0A08BFHSC,DK05,
    SAS,0:1:0,No,278.88 GB,Online,Ok,DELL,ST300MP0005,S7K0WK5P,VT33,
    SAS,0:1:1,No,278.88 GB,Online,Ok,DELL,ST300MP0005,S7K0WK8E,VT33,
    SAS,0:1:0,No,278.88 GB,Online,Ok,DELL,AL13SXB30EN,Z5N0A0FFFHSC,DK05,

    each value demileted by ,  goes to each column .  The problem is 278.88 GB can also come in as 2,278.88 GB. I want to convert that to 2278.88 GB before I parse.

    Saturday, June 15, 2019 2:00 PM
  • Hi Amit,

    create table t1 (c1 varchar(200))
    insert t1 values ('SAS,0:1:1,No,2,278.88 GB,Online,Ok,DELL,AL13SXB30EN,X5T0A03YFHTC,DK05,')

    select *,
    patindex('%[0-9][0-9,][^a-z]%GB%',c1),
    substring(c1,patindex('%[0-9][0-9,][^a-z]%GB%',c1),patindex('%GB%',c1)-patindex('%[0-9][0-9,][^a-z]%GB%',c1))
    from t1

    You can find the position of size in GB using regular expression. I am at home now using sql fiddle but tomorrow from work I can provide more complete solution.






    Saturday, June 15, 2019 2:16 PM
  • Cant I do this using Derived Column? I am trying to avoid SQL queries in my data flow.  So I will have derived column that will replace that comma and then parse the values between columns.

    Please note this is SSIS. I am not writing stored procedure.

    Saturday, June 15, 2019 2:25 PM
  • sorry, you are out of luck as SSIS can't handle regular expression. There are some functions such as findstring, token etc but I would suggest to search the net to find a solution. I have to think about it as I haven't used those functions.
    Saturday, June 15, 2019 2:36 PM
  • Hi AmitSSRS,

    Please try the this expression: REPLACE([Column 0],"2,278.88 GB","2278.88 GB")

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 17, 2019 9:38 AM
  • Hello Mona,

     I dont want to hardcode anything in this expression. 

    Monday, June 17, 2019 2:24 PM
  • Try this:

    replace(substring(column1, findstring(column1, "No", 1)+3, (findstring(column, "Online", 1)-2) - (findstring(column1, "No", 1)+2)),',','')

    Monday, June 17, 2019 7:52 PM