none
Insert data into SQL Table from another SQL Table with pipe delimited as one of the column. Query Help

    Question

  • I want a query for the below scenario.. Please help. 

    Table "A" : 

    Part#   cost     shippinginfo           ShippingCost

    AB12    12.43    12|23|43|34         1.00|3.00|4.00|5.00

    CD34    15.00  56|65|78|89         2.00|4.00|6.00|7.00

    FG23     16.99   45|44|32|23          1.45|2.34|3.45|5.34

    I want to populate Table "B" from Table "A" as follows : 

    Part#   cost     shippinginfo           ShippingCost

    AB12    12.43    12                         1.00

    AB12    12.43    23                         3.00

    AB12    12.43    43                         4.00

    AB12    12.43    34                         5.00

    CD34    15.00  56                         2.00

    CD34    15.00  65                         4.00

    CD34    15.00  78                         6.00

    CD34    15.00   89                        7.00

    FG23     16.99   45                          1.45

    FG23     16.99   44                          2.34

    FG23     16.99   32                           3.45

    FG23     16.99   23                          5.34


    Sunday, September 08, 2013 11:07 PM

Answers

  • Hi,

    Try like this,

    DECLARE @TableA TABLE (Part# VARCHAR(10) ,cost NUMERIC(5,2) ,shippinginfo  VARCHAR(50) ,ShippingCost VARCHAR(50) )
    INSERT @TableA SELECT 'AB12',12.43,' 12|23|43|34','1.00|3.00|4.00|5.00'
    INSERT @TableA SELECT 'CD34',15.00,'56|65|78|89' ,'2.00|4.00|6.00|7.00'
    INSERT @TableA SELECT 'FG23',16.99,'45|44|32|23','1.45|2.34|3.45|5.34'
    --SELECT * FROM @TableA
    DECLARE @TableB TABLE (Part# VARCHAR(10) ,cost NUMERIC(5,2) ,shippinginfo  VARCHAR(50) ,ShippingCost VARCHAR(50) )
    ;WITH CTE1
    AS
    (
    SELECT Part#,
    CAST(('<r><c>' + REPLACE(shippinginfo, '|', '</c></r><r><c>') + '</c></r>' ) AS XML)shippinginfo
    FROM @TableA
    )
    ,CTE2
    AS (
    SELECT Part#,cost,
    CAST(('<r><c>' + REPLACE(ShippingCost, '|', '</c></r><r><c>') + '</c></r>' ) AS XML)ShippingCost
    FROM @TableA
    )
    ,CTE3
    AS
    (
    SELECT Part#, CAST(Col.query('./text()') AS VARCHAR(50))shippinginfo,Row_number()OVER(Partition by Part# Order by Part#) RN
    FROM 
    CTE1 CROSS APPLY 
    shippinginfo.nodes('/r/c') As Tab(Col)
    )
    --SELECT * FROM CTE3
    ,CTE4
    AS
    (
    SELECT Part#, CAST(Col.query('./text()') AS VARCHAR(50))ShippingCost,Row_number()OVER(Partition by Part# Order by Part#) RN,cost
    FROM 
    CTE2 CROSS APPLY 
    ShippingCost.nodes('/r/c') As Tab(Col)
    )
    INSERT @TableB
    SELECT A.Part#,B.cost ,A.shippinginfo,B.ShippingCost FROM CTE3 A
    JOIN CTE4 B ON A.Part# = B.Part# AND  A.RN = B.RN
    SELECT * FROM @TableB


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, September 09, 2013 3:45 AM
    Moderator

All replies

  • Hi,

    Try like this,

    DECLARE @TableA TABLE (Part# VARCHAR(10) ,cost NUMERIC(5,2) ,shippinginfo  VARCHAR(50) ,ShippingCost VARCHAR(50) )
    INSERT @TableA SELECT 'AB12',12.43,' 12|23|43|34','1.00|3.00|4.00|5.00'
    INSERT @TableA SELECT 'CD34',15.00,'56|65|78|89' ,'2.00|4.00|6.00|7.00'
    INSERT @TableA SELECT 'FG23',16.99,'45|44|32|23','1.45|2.34|3.45|5.34'
    --SELECT * FROM @TableA
    DECLARE @TableB TABLE (Part# VARCHAR(10) ,cost NUMERIC(5,2) ,shippinginfo  VARCHAR(50) ,ShippingCost VARCHAR(50) )
    ;WITH CTE1
    AS
    (
    SELECT Part#,
    CAST(('<r><c>' + REPLACE(shippinginfo, '|', '</c></r><r><c>') + '</c></r>' ) AS XML)shippinginfo
    FROM @TableA
    )
    ,CTE2
    AS (
    SELECT Part#,cost,
    CAST(('<r><c>' + REPLACE(ShippingCost, '|', '</c></r><r><c>') + '</c></r>' ) AS XML)ShippingCost
    FROM @TableA
    )
    ,CTE3
    AS
    (
    SELECT Part#, CAST(Col.query('./text()') AS VARCHAR(50))shippinginfo,Row_number()OVER(Partition by Part# Order by Part#) RN
    FROM 
    CTE1 CROSS APPLY 
    shippinginfo.nodes('/r/c') As Tab(Col)
    )
    --SELECT * FROM CTE3
    ,CTE4
    AS
    (
    SELECT Part#, CAST(Col.query('./text()') AS VARCHAR(50))ShippingCost,Row_number()OVER(Partition by Part# Order by Part#) RN,cost
    FROM 
    CTE2 CROSS APPLY 
    ShippingCost.nodes('/r/c') As Tab(Col)
    )
    INSERT @TableB
    SELECT A.Part#,B.cost ,A.shippinginfo,B.ShippingCost FROM CTE3 A
    JOIN CTE4 B ON A.Part# = B.Part# AND  A.RN = B.RN
    SELECT * FROM @TableB


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, September 09, 2013 3:45 AM
    Moderator
  • If the pipe delimeter that can appear in the string is fixed to 3 or below, we can use the below function,

    SELECT

    PARSENAME(REPLACE('12|23|43|34','|','.',1),

    PARSENAME(REPLACE('12|23|43|34','|','.',2),

    PARSENAME(REPLACE('12|23|43|34','|','.',3),

    PARSENAME(REPLACE('12|23|43|34','|','.',4)


    Regards, RSingh

    Monday, September 09, 2013 5:03 AM
  • Please help me with the below scenario as well .

    Table "A":

    Part#         shippinginfo1

    ABC12        1:9.99:5.99|2:11.99:7.99

    DEF34         4:3.44:5.44|6:8.99:9.99

    I want to populate Table "B" from Table "A" as follows :

    Part#         ship_method         ship_cost           handg_cost

    ABC12            1                           9.99                    5.99

    ABC12            2 1                       1.99                      7.99

    DEF34            4                           3.44                    5.44

    DEF34            6                            8.99                    9.99

    Thursday, September 12, 2013 12:42 AM
  • Please help me with the below scenario as well .

    Table "A":

    Part#         shippinginfo1

    ABC12        1:9.99:5.99|2:11.99:7.99

    DEF34         4:3.44:5.44|6:8.99:9.99

    I want to populate Table "B" from Table "A" as follows :

    Part#         ship_method         ship_cost           handg_cost

    ABC12            1                           9.99                    5.99

    ABC12            2 1                       1.99                      7.99

    DEF34            4                           3.44                    5.44

    DEF34            6                            8.99                    9.99

    Hi ,

    I hope it is being answered here . Duplicate thread .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Thursday, September 12, 2013 3:24 AM
    Moderator