Loading ...
Sorry, an error occurred while loading the content.

Re: Elegant technique?

Expand Messages
  • Damhuis Anton
    Hi Based on what Paul did, I added xml tags. I know somehow one can read xml tags from the later version of MS SQL. I have come up with this so far: --Begin
    Message 1 of 17 , Jun 1, 2011
    • 0 Attachment
      Hi

      Based on what Paul did, I added xml tags. I know somehow one can read xml tags from the later version of MS SQL.

      I have come up with this so far:

      --Begin SQL Code
      Create table Table1 (
      PO int,
      ID int,
      Comment varchar(50)
      )

      Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST');
      Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR');
      Insert into Table1 Values (1234567890, 3, 'Y US')

      SELECT
      W2.PO,
      SUBSTRING(
      Replace(
      (
      SELECT
      '' + T1.Comment
      FROM
      Table1 as T1
      WHERE
      T1.PO = W2.PO
      ORDER
      BY PO ASC, ID ASC
      FOR
      XML PATH('')
      ),
      'X', '</tag><tag>')
      ,7,1000)
      + '</tag>' as Comment
      FROM
      Table1 W2
      GROUP BY PO


      Drop table Table1
      --End SQL Code

      I still need to figure out how to read the xml tags into proper table columns. Maybe one of the other boffins can improve.

      Regards
      Anton Damhuis

      [cid:image001.jpg@01CC2076.7BA01420]

      Building 1, Inanda Greens Office Park, 54 Wierda Road West
      Wierde Valley, PO Box 786055, Sandton 2146
      e-mail: DamhuisAt@...<mailto:DamhuisA@...>
      switchboard: +27 (11) 505 6000
      telephone: +27 (11) 505 6326

      www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
      Investment Solutions Limited is a licensed financial service provider. FAIS license number 711




      ________________________________
      Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


      [Non-text portions of this message have been removed]
    • Andrew Mills
      There is an XML datatype for this in SQL Server 2008 and I believe SQL Server 2005 (but not 100% of this). Thanks Andy From: SQLQueriesNoCode@yahoogroups.com
      Message 2 of 17 , Jun 1, 2011
      • 0 Attachment
        There is an XML datatype for this in SQL Server 2008 and I believe SQL
        Server 2005 (but not 100% of this).



        Thanks

        Andy



        From: SQLQueriesNoCode@yahoogroups.com
        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Damhuis Anton
        Sent: Wednesday, June 01, 2011 10:11 AM
        To: SQLQueriesNoCode@yahoogroups.com
        Subject: [SQLQueriesNoCode] Re: Elegant technique?





        Hi

        Based on what Paul did, I added xml tags. I know somehow one can read xml
        tags from the later version of MS SQL.

        I have come up with this so far:

        --Begin SQL Code
        Create table Table1 (
        PO int,
        ID int,
        Comment varchar(50)
        )

        Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way
        XCITY Flagstaff XST');
        Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286
        XSHIP TO COUNTR');
        Insert into Table1 Values (1234567890, 3, 'Y US')

        SELECT
        W2.PO,
        SUBSTRING(
        Replace(
        (
        SELECT
        '' + T1.Comment
        FROM
        Table1 as T1
        WHERE
        T1.PO = W2.PO
        ORDER
        BY PO ASC, ID ASC
        FOR
        XML PATH('')
        ),
        'X', '</tag><tag>')
        ,7,1000)
        + '</tag>' as Comment
        FROM
        Table1 W2
        GROUP BY PO

        Drop table Table1
        --End SQL Code

        I still need to figure out how to read the xml tags into proper table
        columns. Maybe one of the other boffins can improve.

        Regards
        Anton Damhuis

        [cid:image001.jpg@01CC2076.7BA01420
        <mailto:image001.jpg%4001CC2076.7BA01420> ]

        Building 1, Inanda Greens Office Park, 54 Wierda Road West
        Wierde Valley, PO Box 786055, Sandton 2146
        e-mail: DamhuisAt@... <mailto:DamhuisAt%40ishltd.co.za>
        <mailto:DamhuisA@... <mailto:DamhuisA%40ishltd.co.za> >
        switchboard: +27 (11) 505 6000
        telephone: +27 (11) 505 6326

        www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
        Investment Solutions Limited is a licensed financial service provider. FAIS
        license number 711

        ________________________________
        Please note: This email and its contents are subject to the disclaimer as
        displayed at the following link:
        http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you
        not have web access, send an email to legalnotice@...
        <mailto:legalnotice%40ishltd.co.za> and a copy of this disclaimer will be
        sent to you.

        [Non-text portions of this message have been removed]





        [Non-text portions of this message have been removed]
      • Travis Truax
        Hi Paul, Yes, it s on a MSSQL2008 box. In the cursor-based routine, I ve just got a borderline illegible select statement filled with the following: SELECT
        Message 3 of 17 , Jun 1, 2011
        • 0 Attachment
          Hi Paul,
          Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got a borderline illegible select statement filled with the following:

          SELECT
          SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS vendor_order_no
          ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NBR%',SOP_COMMENT)+12,13) AS member_no
          ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS ship_to_name
          ......
          FROM erp_utilities.dbo.sop_order_header_comments_view
          WHERE ORDER_NUMBER = @sales_order_number
          AND PATINDEX('%XCLUB%',SOP_COMMENT) > 0


          Thanks, I hadn't realized that you could use the XML PATH clause to get around the ordered inline views issue.

          Travis-



          ----- Original Message -----

          From: "Paul Livengood" <p_livengood@...>
          To: SQLQueriesNoCode@yahoogroups.com
          Sent: Tuesday, May 31, 2011 4:35:25 PM
          Subject: Re: [SQLQueriesNoCode] Elegant technique?

          Assuming SQL 2005 or later then concatenating all the ocmment lines for each order is simple (see example at end), but grabing the substrings out between the X column identifiers will be difficult because there is no logic to it. X is a HORRIBLE deliminator because it can be used in normal words.

          Concatenate example
          SELECT w1.PO,
          (SELECT '' + w2.Comment
          FROM {Table Name} w2
          WHERE w1.PO = w2.PO
          ORDER BY PO, ID
          FOR XML PATH(''))
          FROM {Table Name} w1
          GROUP BY PO


          If you can supply more information on how you are spliting out the xColumn names I may be able to help make this set based instead of cursor based.

          HTH
          Paul

          From: Travis Truax <travis.truax@...>
          To: SQLQueriesNoCode@yahoogroups.com
          Sent: Tuesday, May 31, 2011 1:43 PM
          Subject: [SQLQueriesNoCode] Elegant technique?




          The order information we get from a certain .com retailer comes to us via EDI as a single merged string, chopped up into rows of data that are limited to something like 64 characters, encoded with field names identified by a preceding "X". Like so:

          po id comment
          1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
          1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
          1234567890 3 Y US

          It's really ugly, but I have to deal with it - it's not going to change.
          This is text processing that many folks would prefer to handle before it gets pushed into the DB, but we prefer to get it in the DB and then process it for other reasons.

          We need to concatenate all the comment lines for each order, sequenced by id, and then grab substrings out between the "X" column identifiers.

          I already have a kludgy cursor-based method for this, but I was wondering if there were any ways to do this that were more efficient. Like some way to concatenate all rows within a group or something similar. Even if that were possible, the rows must also be concatenated in proper sequence.
          I don't think SQL was really designed for this type of processing, but I thought I'd ask... sometimes you can do surprisingly well with the wrong tool for the job, and sometimes you just injure yourself. ;)

          Thanks,
          Travis-

          [Non-text portions of this message have been removed]




          [Non-text portions of this message have been removed]



          ------------------------------------

          Yahoo! Groups Links






          [Non-text portions of this message have been removed]
        • Travis Truax
          I have to plead ignorance of the XML functionality in SQL Server. I knew something was there, but I thought that if I left it alone, it might leave me alone.
          Message 4 of 17 , Jun 1, 2011
          • 0 Attachment
            I have to plead ignorance of the XML functionality in SQL Server. I knew "something" was there, but I thought that if I left it alone, it might leave me alone. :)

            Travis-

            ----- Original Message -----

            From: "Andrew Mills" <amills@...>
            To: SQLQueriesNoCode@yahoogroups.com
            Sent: Wednesday, June 1, 2011 9:20:34 AM
            Subject: RE: [SQLQueriesNoCode] Re: Elegant technique?

            There is an XML datatype for this in SQL Server 2008 and I believe SQL
            Server 2005 (but not 100% of this).



            Thanks

            Andy



            From: SQLQueriesNoCode@yahoogroups.com
            [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Damhuis Anton
            Sent: Wednesday, June 01, 2011 10:11 AM
            To: SQLQueriesNoCode@yahoogroups.com
            Subject: [SQLQueriesNoCode] Re: Elegant technique?





            Hi

            Based on what Paul did, I added xml tags. I know somehow one can read xml
            tags from the later version of MS SQL.

            I have come up with this so far:

            --Begin SQL Code
            Create table Table1 (
            PO int,
            ID int,
            Comment varchar(50)
            )

            Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way
            XCITY Flagstaff XST');
            Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286
            XSHIP TO COUNTR');
            Insert into Table1 Values (1234567890, 3, 'Y US')

            SELECT
            W2.PO,
            SUBSTRING(
            Replace(
            (
            SELECT
            '' + T1.Comment
            FROM
            Table1 as T1
            WHERE
            T1.PO = W2.PO
            ORDER
            BY PO ASC, ID ASC
            FOR
            XML PATH('')
            ),
            'X', '</tag><tag>')
            ,7,1000)
            + '</tag>' as Comment
            FROM
            Table1 W2
            GROUP BY PO

            Drop table Table1
            --End SQL Code

            I still need to figure out how to read the xml tags into proper table
            columns. Maybe one of the other boffins can improve.

            Regards
            Anton Damhuis

            [cid:image001.jpg@01CC2076.7BA01420
            <mailto:image001.jpg%4001CC2076.7BA01420> ]

            Building 1, Inanda Greens Office Park, 54 Wierda Road West
            Wierde Valley, PO Box 786055, Sandton 2146
            e-mail: DamhuisAt@... <mailto:DamhuisAt%40ishltd.co.za>
            <mailto:DamhuisA@... <mailto:DamhuisA%40ishltd.co.za> >
            switchboard: +27 (11) 505 6000
            telephone: +27 (11) 505 6326

            www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
            Investment Solutions Limited is a licensed financial service provider. FAIS
            license number 711

            ________________________________
            Please note: This email and its contents are subject to the disclaimer as
            displayed at the following link:
            http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you
            not have web access, send an email to legalnotice@...
            <mailto:legalnotice%40ishltd.co.za> and a copy of this disclaimer will be
            sent to you.

            [Non-text portions of this message have been removed]





            [Non-text portions of this message have been removed]



            ------------------------------------

            Yahoo! Groups Links






            [Non-text portions of this message have been removed]
          • Damhuis Anton
            Ok this is what I worked it out as: --Begin SQL Code -- Drop table Table1 Create table Table1 ( PO int, ID int, Comment varchar(50) ) Insert into Table1 Values
            Message 5 of 17 , Jun 1, 2011
            • 0 Attachment
              Ok this is what I worked it out as:

              --Begin SQL Code
              -- Drop table Table1

              Create table Table1 (
              PO int,
              ID int,
              Comment varchar(50)
              )

              Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST');
              Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR');
              Insert into Table1 Values (1234567890, 3, 'Y US')

              Select
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[1]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column1,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[2]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column2,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[3]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column3,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[4]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column4,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[5]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column5,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[6]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column6,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[7]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column7,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[8]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column7,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[9]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column9,
              LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[10]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column10
              from
              (
              SELECT
              W2.PO,
              CAST(
              '<tags>' +
              SUBSTRING(
              Replace(
              (
              SELECT
              '' + T1.Comment
              FROM
              Table1 as T1
              WHERE
              T1.PO = W2.PO
              ORDER
              BY PO ASC, ID ASC
              FOR
              XML PATH('')
              ),
              'X', '</tag><tag>')
              ,7,1000)
              + '</tag></tags>'
              as XML) as Comment
              FROM
              Table1 W2
              GROUP BY PO
              ) as Table2

              Drop table Table1
              --End SQL Code

              Maybe needs to be optimised in some way? Suggestions?
              Would be interesting to know with all the casting going on, if it would be faster than a cursor.

              Regards
              Anton Damhuis

              [cid:image001.jpg@01CC207A.2F0816E0]

              Building 1, Inanda Greens Office Park, 54 Wierda Road West
              Wierde Valley, PO Box 786055, Sandton 2146
              e-mail: DamhuisAt@...<mailto:DamhuisA@...>
              switchboard: +27 (11) 505 6000
              telephone: +27 (11) 505 6326

              www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
              Investment Solutions Limited is a licensed financial service provider. FAIS license number 711




              ________________________________
              Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


              [Non-text portions of this message have been removed]
            • Damhuis Anton
              Oops sorry forgot to add: This linked helped me:
              Message 6 of 17 , Jun 1, 2011
              • 0 Attachment
                Oops sorry forgot to add:

                This linked helped me:
                http://beyondrelational.com/blogs/jacob/archive/2008/09/14/xquery-lab-34-how-to-retrieve-the-child-element-at-a-specified-position.aspx


                Regards
                Anton






                ________________________________
                Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


                [Non-text portions of this message have been removed]
              • Paul Livengood
                its not going to be pretty no matter what because we will ahve to hard code field names and lenghs. Can you supply all the possible field names and do they
                Message 7 of 17 , Jun 1, 2011
                • 0 Attachment
                  its not going to be pretty no matter what because we will ahve to hard code field names and lenghs.
                  Can you supply all the possible field names and do they always come in a specific order (or are they mixed up)?
                   

                  From: Travis Truax <travis.truax@...>
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Wednesday, June 1, 2011 8:23 AM
                  Subject: Re: [SQLQueriesNoCode] Elegant technique?


                   

                  Hi Paul,
                  Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got a borderline illegible select statement filled with the following:

                  SELECT
                  SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS vendor_order_no
                  ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NBR%',SOP_COMMENT)+12,13) AS member_no
                  ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS ship_to_name
                  ......
                  FROM erp_utilities.dbo.sop_order_header_comments_view
                  WHERE ORDER_NUMBER = @sales_order_number
                  AND PATINDEX('%XCLUB%',SOP_COMMENT) > 0

                  Thanks, I hadn't realized that you could use the XML PATH clause to get around the ordered inline views issue.

                  Travis-

                  ----- Original Message -----

                  From: "Paul Livengood" <p_livengood@...>
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Tuesday, May 31, 2011 4:35:25 PM
                  Subject: Re: [SQLQueriesNoCode] Elegant technique?

                  Assuming SQL 2005 or later then concatenating all the ocmment lines for each order is simple (see example at end), but grabing the substrings out between the X column identifiers will be difficult because there is no logic to it. X is a HORRIBLE deliminator because it can be used in normal words.

                  Concatenate example
                  SELECT w1.PO,
                  (SELECT '' + w2.Comment
                  FROM {Table Name} w2
                  WHERE w1.PO = w2.PO
                  ORDER BY PO, ID
                  FOR XML PATH(''))
                  FROM {Table Name} w1
                  GROUP BY PO

                  If you can supply more information on how you are spliting out the xColumn names I may be able to help make this set based instead of cursor based.

                  HTH
                  Paul

                  From: Travis Truax <travis.truax@...>
                  To: SQLQueriesNoCode@yahoogroups.com
                  Sent: Tuesday, May 31, 2011 1:43 PM
                  Subject: [SQLQueriesNoCode] Elegant technique?

                  The order information we get from a certain .com retailer comes to us via EDI as a single merged string, chopped up into rows of data that are limited to something like 64 characters, encoded with field names identified by a preceding "X". Like so:

                  po id comment
                  1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
                  1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
                  1234567890 3 Y US

                  It's really ugly, but I have to deal with it - it's not going to change.
                  This is text processing that many folks would prefer to handle before it gets pushed into the DB, but we prefer to get it in the DB and then process it for other reasons.

                  We need to concatenate all the comment lines for each order, sequenced by id, and then grab substrings out between the "X" column identifiers.

                  I already have a kludgy cursor-based method for this, but I was wondering if there were any ways to do this that were more efficient. Like some way to concatenate all rows within a group or something similar. Even if that were possible, the rows must also be concatenated in proper sequence.
                  I don't think SQL was really designed for this type of processing, but I thought I'd ask... sometimes you can do surprisingly well with the wrong tool for the job, and sometimes you just injure yourself. ;)

                  Thanks,
                  Travis-

                  [Non-text portions of this message have been removed]

                  [Non-text portions of this message have been removed]

                  ------------------------------------

                  Yahoo! Groups Links

                  [Non-text portions of this message have been removed]




                  [Non-text portions of this message have been removed]
                • Travis Truax
                  Thanks Anton, Something else has come up so I can t work on merging any of the suggested solutions into my code at the moment, but I ll post what worked best
                  Message 8 of 17 , Jun 1, 2011
                  • 0 Attachment
                    Thanks Anton,
                    Something else has come up so I can't work on merging any of the suggested solutions into my code at the moment, but I'll post what worked best for me when I get back to it.

                    Travis-

                    ----- Original Message -----

                    From: "Damhuis Anton" <DamhuisA@...>
                    To: SQLQueriesNoCode@yahoogroups.com
                    Sent: Wednesday, June 1, 2011 9:37:23 AM
                    Subject: [SQLQueriesNoCode] Re: Elegant technique?

                    Ok this is what I worked it out as:

                    --Begin SQL Code
                    -- Drop table Table1

                    Create table Table1 (
                    PO int,
                    ID int,
                    Comment varchar(50)
                    )

                    Insert into Table1 Values (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST');
                    Insert into Table1 Values (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR');
                    Insert into Table1 Values (1234567890, 3, 'Y US')

                    Select
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[1]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column1,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[2]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column2,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[3]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column3,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[4]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column4,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[5]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column5,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[6]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column6,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[7]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column7,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[8]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column7,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[9]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column9,
                    LTrim(RTrim(Replace(Replace(Cast(Comment.query('/tags/tag[10]') as varchar(100)),'<tag>', ''),'</tag>',''))) as Column10
                    from
                    (
                    SELECT
                    W2.PO,
                    CAST(
                    '<tags>' +
                    SUBSTRING(
                    Replace(
                    (
                    SELECT
                    '' + T1.Comment
                    FROM
                    Table1 as T1
                    WHERE
                    T1.PO = W2.PO
                    ORDER
                    BY PO ASC, ID ASC
                    FOR
                    XML PATH('')
                    ),
                    'X', '</tag><tag>')
                    ,7,1000)
                    + '</tag></tags>'
                    as XML) as Comment
                    FROM
                    Table1 W2
                    GROUP BY PO
                    ) as Table2

                    Drop table Table1
                    --End SQL Code

                    Maybe needs to be optimised in some way? Suggestions?
                    Would be interesting to know with all the casting going on, if it would be faster than a cursor.

                    Regards
                    Anton Damhuis

                    [cid:image001.jpg@01CC207A.2F0816E0]

                    Building 1, Inanda Greens Office Park, 54 Wierda Road West
                    Wierde Valley, PO Box 786055, Sandton 2146
                    e-mail: DamhuisAt@...<mailto:DamhuisA@...>
                    switchboard: +27 (11) 505 6000
                    telephone: +27 (11) 505 6326

                    www.investmentsolutions.co.za<http://www.investmentsolutions.co.za>
                    Investment Solutions Limited is a licensed financial service provider. FAIS license number 711




                    ________________________________
                    Please note: This email and its contents are subject to the disclaimer as displayed at the following link: http://sa.investmentsolutions.co.za/pages/emaillegalnotice.aspx. Should you not have web access, send an email to legalnotice@... and a copy of this disclaimer will be sent to you.


                    [Non-text portions of this message have been removed]



                    ------------------------------------

                    Yahoo! Groups Links






                    [Non-text portions of this message have been removed]
                  • Travis Truax
                    This is the entire field list: (please don t bother coding more in detail, I was just looking for suggestions. I know you guys have stuff to do... ) Your
                    Message 9 of 17 , Jun 1, 2011
                    • 0 Attachment
                      This is the entire field list: (please don't bother coding more in detail, I was just looking for suggestions. I know you guys have stuff to do... ) Your inline view looks like it's going to do the job just fine.

                      XCLUB
                      XORDER
                      XMEMBER NBR
                      XMEMBER NAME
                      XSHIP TO NAME
                      XSHIP TO ADDR1
                      XSHIP TO ADDR2
                      XSHIP TO ADDR3
                      XSHIP_TO_CITY
                      XSHIP_TO_STATE
                      XSHIP_TO_ZIP-CODE
                      XSHIP_TO_PHONE
                      XSHIP_TO_COUNTRY

                      They do appear in order, however each is not always present (such as Address 2 & 3) and some can have slightly different formats (such as zip-code). I just handled that stuff with CASE statements in my query like:

                      ,CASE
                      --When the Address2 field exists - use it as the delimiter for the Address1 field.
                      WHEN PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0
                      THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR2%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14))
                      --When the Address3 field exists, without the Address2 field - use it as the delimiter for the Address1 field.
                      WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) = 0
                      THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14))
                      --When neither Address 2 or 3 fields exist - use the City field as the delimiter.
                      ELSE SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)+14))
                      END AS ship_to_addr1
                      ,CASE --If Address 2 field exists, but Address 3 field does not exist - use the City field as the delimiter for Address 2, otherwise do not include Address2 at all.
                      WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) = 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT)+14))
                      WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT)+14))
                      ELSE ''
                      END AS ship_to_addr2
                      ,CASE
                      WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT)+14))
                      ELSE ''
                      END AS ship_to_addr3


                      Travis-

                      ----- Original Message -----

                      From: "Paul Livengood" <p_livengood@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Wednesday, June 1, 2011 9:40:27 AM
                      Subject: Re: [SQLQueriesNoCode] Elegant technique?

                      its not going to be pretty no matter what because we will ahve to hard code field names and lenghs.
                      Can you supply all the possible field names and do they always come in a specific order (or are they mixed up)?


                      From: Travis Truax <travis.truax@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Wednesday, June 1, 2011 8:23 AM
                      Subject: Re: [SQLQueriesNoCode] Elegant technique?




                      Hi Paul,
                      Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got a borderline illegible select statement filled with the following:

                      SELECT
                      SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS vendor_order_no
                      ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NBR%',SOP_COMMENT)+12,13) AS member_no
                      ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS ship_to_name
                      ......
                      FROM erp_utilities.dbo.sop_order_header_comments_view
                      WHERE ORDER_NUMBER = @sales_order_number
                      AND PATINDEX('%XCLUB%',SOP_COMMENT) > 0

                      Thanks, I hadn't realized that you could use the XML PATH clause to get around the ordered inline views issue.

                      Travis-

                      ----- Original Message -----

                      From: "Paul Livengood" <p_livengood@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Tuesday, May 31, 2011 4:35:25 PM
                      Subject: Re: [SQLQueriesNoCode] Elegant technique?

                      Assuming SQL 2005 or later then concatenating all the ocmment lines for each order is simple (see example at end), but grabing the substrings out between the X column identifiers will be difficult because there is no logic to it. X is a HORRIBLE deliminator because it can be used in normal words.

                      Concatenate example
                      SELECT w1.PO,
                      (SELECT '' + w2.Comment
                      FROM {Table Name} w2
                      WHERE w1.PO = w2.PO
                      ORDER BY PO, ID
                      FOR XML PATH(''))
                      FROM {Table Name} w1
                      GROUP BY PO

                      If you can supply more information on how you are spliting out the xColumn names I may be able to help make this set based instead of cursor based.

                      HTH
                      Paul

                      From: Travis Truax <travis.truax@...>
                      To: SQLQueriesNoCode@yahoogroups.com
                      Sent: Tuesday, May 31, 2011 1:43 PM
                      Subject: [SQLQueriesNoCode] Elegant technique?

                      The order information we get from a certain .com retailer comes to us via EDI as a single merged string, chopped up into rows of data that are limited to something like 64 characters, encoded with field names identified by a preceding "X". Like so:

                      po id comment
                      1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
                      1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
                      1234567890 3 Y US

                      It's really ugly, but I have to deal with it - it's not going to change.
                      This is text processing that many folks would prefer to handle before it gets pushed into the DB, but we prefer to get it in the DB and then process it for other reasons.

                      We need to concatenate all the comment lines for each order, sequenced by id, and then grab substrings out between the "X" column identifiers.

                      I already have a kludgy cursor-based method for this, but I was wondering if there were any ways to do this that were more efficient. Like some way to concatenate all rows within a group or something similar. Even if that were possible, the rows must also be concatenated in proper sequence.
                      I don't think SQL was really designed for this type of processing, but I thought I'd ask... sometimes you can do surprisingly well with the wrong tool for the job, and sometimes you just injure yourself. ;)

                      Thanks,
                      Travis-

                      [Non-text portions of this message have been removed]

                      [Non-text portions of this message have been removed]

                      ------------------------------------

                      Yahoo! Groups Links

                      [Non-text portions of this message have been removed]




                      [Non-text portions of this message have been removed]



                      ------------------------------------

                      Yahoo! Groups Links






                      [Non-text portions of this message have been removed]
                    • Arnie Rowland
                      This gets you a comma delimited Name/Value pair list. USE tempdb; GO INSERT INTO TestTable VALUES (1234567890, 1, XSHIP TO ADDR2 3500 Amiel Way XCITY
                      Message 10 of 17 , Jun 1, 2011
                      • 0 Attachment
                        This gets you a comma delimited Name/Value pair list.

                        USE tempdb;
                        GO

                        INSERT INTO TestTable
                        VALUES
                        (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff
                        XST'),
                        (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR'),
                        (1234567890, 3, 'Y US');

                        SELECT
                        PO,
                        replace( Comment, ' X', ',') AS 'Comment'
                        FROM ( SELECT
                        t1.PO,
                        Comment = substring((SELECT ( '' + Comment )
                        FROM TestTable t2
                        WHERE t1.PO = t2.PO
                        ORDER BY
                        PO,
                        ID
                        FOR XML PATH( '' )
                        ), 2, 1000 ) FROM TestTable t1
                        GROUP BY PO
                        ) t3

                        Then you can use a split function to separate the Name/Value pairs.
                        See:
                        http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Strin
                        gArrayInput

                        You can then use REPLACE() to remove the column names, or just suppress
                        them in the report (labels/invoices). IF you choose to use REPLACE(), I
                        suggest putting the known Column Name values in a table, and cycle
                        through the table. Much better than 'hard coding'...


                        Regards,

                        Arnie Rowland, MVP (SQL Server)

                        "You cannot do a kindness too soon because you never know how soon it
                        will be too late."
                        -Ralph Waldo Emerson




                        -----Original Message-----
                        From: SQLQueriesNoCode@yahoogroups.com
                        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                        Sent: Wednesday, June 01, 2011 8:08 AM
                        To: SQLQueriesNoCode@yahoogroups.com
                        Subject: Re: [SQLQueriesNoCode] Elegant technique?

                        This is the entire field list: (please don't bother coding more in
                        detail, I was just looking for suggestions. I know you guys have stuff
                        to do... ) Your inline view looks like it's going to do the job just
                        fine.

                        XCLUB
                        XORDER
                        XMEMBER NBR
                        XMEMBER NAME
                        XSHIP TO NAME
                        XSHIP TO ADDR1
                        XSHIP TO ADDR2
                        XSHIP TO ADDR3
                        XSHIP_TO_CITY
                        XSHIP_TO_STATE
                        XSHIP_TO_ZIP-CODE
                        XSHIP_TO_PHONE
                        XSHIP_TO_COUNTRY

                        They do appear in order, however each is not always present (such as
                        Address 2 & 3) and some can have slightly different formats (such as
                        zip-code). I just handled that stuff with CASE statements in my query
                        like:

                        ,CASE
                        --When the Address2 field exists - use it as the delimiter for the
                        Address1 field.
                        WHEN PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0 THEN
                        SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR2%',SOP_COMMENT)-(PATIND
                        EX('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) --When the Address3 field
                        exists, without the Address2 field - use it as the delimiter for the
                        Address1 field.
                        WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP
                        TO ADDR2%',SOP_COMMENT) = 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP
                        TO
                        ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATIND
                        EX('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) --When neither Address 2 or 3
                        fields exist - use the City field as the delimiter.
                        ELSE SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                        X('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) END AS ship_to_addr1 ,CASE --If
                        Address 2 field exists, but Address 3 field does not exist - use the
                        City field as the delimiter for Address 2, otherwise do not include
                        Address2 at all.
                        WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) = 0 AND PATINDEX('%XSHIP
                        TO ADDR2%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP
                        TO
                        ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                        X('%XSHIP TO ADDR2%',SOP_COMMENT)+14)) WHEN PATINDEX('%XSHIP TO
                        ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) >
                        0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATIND
                        EX('%XSHIP TO ADDR2%',SOP_COMMENT)+14)) ELSE ''
                        END AS ship_to_addr2
                        ,CASE
                        WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 THEN
                        SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        ADDR3%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                        X('%XSHIP TO ADDR3%',SOP_COMMENT)+14)) ELSE ''
                        END AS ship_to_addr3


                        Travis-

                        ----- Original Message -----

                        From: "Paul Livengood" <p_livengood@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Wednesday, June 1, 2011 9:40:27 AM
                        Subject: Re: [SQLQueriesNoCode] Elegant technique?

                        its not going to be pretty no matter what because we will ahve to hard
                        code field names and lenghs.
                        Can you supply all the possible field names and do they always come in a
                        specific order (or are they mixed up)?


                        From: Travis Truax <travis.truax@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Wednesday, June 1, 2011 8:23 AM
                        Subject: Re: [SQLQueriesNoCode] Elegant technique?




                        Hi Paul,
                        Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got
                        a borderline illegible select statement filled with the following:

                        SELECT
                        SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS
                        vendor_order_no ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER
                        NBR%',SOP_COMMENT)+12,13) AS member_no
                        ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER
                        NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO
                        NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS
                        member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                        NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO
                        ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS
                        ship_to_name ......
                        FROM erp_utilities.dbo.sop_order_header_comments_view
                        WHERE ORDER_NUMBER = @sales_order_number AND
                        PATINDEX('%XCLUB%',SOP_COMMENT) > 0

                        Thanks, I hadn't realized that you could use the XML PATH clause to get
                        around the ordered inline views issue.

                        Travis-

                        ----- Original Message -----

                        From: "Paul Livengood" <p_livengood@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Tuesday, May 31, 2011 4:35:25 PM
                        Subject: Re: [SQLQueriesNoCode] Elegant technique?

                        Assuming SQL 2005 or later then concatenating all the ocmment lines for
                        each order is simple (see example at end), but grabing the substrings
                        out between the X column identifiers will be difficult because there is
                        no logic to it. X is a HORRIBLE deliminator because it can be used in
                        normal words.

                        Concatenate example
                        SELECT w1.PO,
                        (SELECT '' + w2.Comment
                        FROM {Table Name} w2
                        WHERE w1.PO = w2.PO
                        ORDER BY PO, ID
                        FOR XML PATH(''))
                        FROM {Table Name} w1
                        GROUP BY PO

                        If you can supply more information on how you are spliting out the
                        xColumn names I may be able to help make this set based instead of
                        cursor based.

                        HTH
                        Paul

                        From: Travis Truax <travis.truax@...>
                        To: SQLQueriesNoCode@yahoogroups.com
                        Sent: Tuesday, May 31, 2011 1:43 PM
                        Subject: [SQLQueriesNoCode] Elegant technique?

                        The order information we get from a certain .com retailer comes to us
                        via EDI as a single merged string, chopped up into rows of data that are
                        limited to something like 64 characters, encoded with field names
                        identified by a preceding "X". Like so:

                        po id comment
                        1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
                        1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
                        1234567890 3 Y US

                        It's really ugly, but I have to deal with it - it's not going to change.

                        This is text processing that many folks would prefer to handle before it
                        gets pushed into the DB, but we prefer to get it in the DB and then
                        process it for other reasons.

                        We need to concatenate all the comment lines for each order, sequenced
                        by id, and then grab substrings out between the "X" column identifiers.

                        I already have a kludgy cursor-based method for this, but I was
                        wondering if there were any ways to do this that were more efficient.
                        Like some way to concatenate all rows within a group or something
                        similar. Even if that were possible, the rows must also be concatenated
                        in proper sequence.
                        I don't think SQL was really designed for this type of processing, but I
                        thought I'd ask... sometimes you can do surprisingly well with the wrong
                        tool for the job, and sometimes you just injure yourself. ;)

                        Thanks,
                        Travis-

                        [Non-text portions of this message have been removed]

                        [Non-text portions of this message have been removed]

                        ------------------------------------

                        Yahoo! Groups Links

                        [Non-text portions of this message have been removed]




                        [Non-text portions of this message have been removed]



                        ------------------------------------

                        Yahoo! Groups Links






                        [Non-text portions of this message have been removed]



                        ------------------------------------

                        Yahoo! Groups Links







                        [Non-text portions of this message have been removed]
                      • Travis Truax
                        Thanks Arnie, I m actually just parsing the data they ve made sausage out of and inserting it into a shipping table to integrate with Fedex & UPS software
                        Message 11 of 17 , Jun 1, 2011
                        • 0 Attachment
                          Thanks Arnie,
                          I'm actually just parsing the data they've made sausage out of and inserting it into a shipping table to integrate with Fedex & UPS software among other things, so the field names aren't needed.

                          Travis-

                          ----- Original Message -----

                          From: "Arnie Rowland" <arnie@...>
                          To: SQLQueriesNoCode@yahoogroups.com
                          Sent: Wednesday, June 1, 2011 11:23:02 AM
                          Subject: RE: [SQLQueriesNoCode] Elegant technique?

                          This gets you a comma delimited Name/Value pair list.

                          USE tempdb;
                          GO

                          INSERT INTO TestTable
                          VALUES
                          (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff
                          XST'),
                          (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR'),
                          (1234567890, 3, 'Y US');

                          SELECT
                          PO,
                          replace( Comment, ' X', ',') AS 'Comment'
                          FROM ( SELECT
                          t1.PO,
                          Comment = substring((SELECT ( '' + Comment )
                          FROM TestTable t2
                          WHERE t1.PO = t2.PO
                          ORDER BY
                          PO,
                          ID
                          FOR XML PATH( '' )
                          ), 2, 1000 ) FROM TestTable t1
                          GROUP BY PO
                          ) t3

                          Then you can use a split function to separate the Name/Value pairs.
                          See:
                          http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Strin
                          gArrayInput

                          You can then use REPLACE() to remove the column names, or just suppress
                          them in the report (labels/invoices). IF you choose to use REPLACE(), I
                          suggest putting the known Column Name values in a table, and cycle
                          through the table. Much better than 'hard coding'...


                          Regards,

                          Arnie Rowland, MVP (SQL Server)

                          "You cannot do a kindness too soon because you never know how soon it
                          will be too late."
                          -Ralph Waldo Emerson




                          -----Original Message-----
                          From: SQLQueriesNoCode@yahoogroups.com
                          [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Travis Truax
                          Sent: Wednesday, June 01, 2011 8:08 AM
                          To: SQLQueriesNoCode@yahoogroups.com
                          Subject: Re: [SQLQueriesNoCode] Elegant technique?

                          This is the entire field list: (please don't bother coding more in
                          detail, I was just looking for suggestions. I know you guys have stuff
                          to do... ) Your inline view looks like it's going to do the job just
                          fine.

                          XCLUB
                          XORDER
                          XMEMBER NBR
                          XMEMBER NAME
                          XSHIP TO NAME
                          XSHIP TO ADDR1
                          XSHIP TO ADDR2
                          XSHIP TO ADDR3
                          XSHIP_TO_CITY
                          XSHIP_TO_STATE
                          XSHIP_TO_ZIP-CODE
                          XSHIP_TO_PHONE
                          XSHIP_TO_COUNTRY

                          They do appear in order, however each is not always present (such as
                          Address 2 & 3) and some can have slightly different formats (such as
                          zip-code). I just handled that stuff with CASE statements in my query
                          like:

                          ,CASE
                          --When the Address2 field exists - use it as the delimiter for the
                          Address1 field.
                          WHEN PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) > 0 THEN
                          SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                          ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR2%',SOP_COMMENT)-(PATIND
                          EX('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) --When the Address3 field
                          exists, without the Address2 field - use it as the delimiter for the
                          Address1 field.
                          WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP
                          TO ADDR2%',SOP_COMMENT) = 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP
                          TO
                          ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATIND
                          EX('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) --When neither Address 2 or 3
                          fields exist - use the City field as the delimiter.
                          ELSE SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                          ADDR1%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                          X('%XSHIP TO ADDR1%',SOP_COMMENT)+14)) END AS ship_to_addr1 ,CASE --If
                          Address 2 field exists, but Address 3 field does not exist - use the
                          City field as the delimiter for Address 2, otherwise do not include
                          Address2 at all.
                          WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) = 0 AND PATINDEX('%XSHIP
                          TO ADDR2%',SOP_COMMENT) > 0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP
                          TO
                          ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                          X('%XSHIP TO ADDR2%',SOP_COMMENT)+14)) WHEN PATINDEX('%XSHIP TO
                          ADDR3%',SOP_COMMENT) > 0 AND PATINDEX('%XSHIP TO ADDR2%',SOP_COMMENT) >
                          0 THEN SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                          ADDR2%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_ADDR3%',SOP_COMMENT)-(PATIND
                          EX('%XSHIP TO ADDR2%',SOP_COMMENT)+14)) ELSE ''
                          END AS ship_to_addr2
                          ,CASE
                          WHEN PATINDEX('%XSHIP TO ADDR3%',SOP_COMMENT) > 0 THEN
                          SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                          ADDR3%',SOP_COMMENT)+14,PATINDEX('%XSHIP_TO_CITY%',SOP_COMMENT)-(PATINDE
                          X('%XSHIP TO ADDR3%',SOP_COMMENT)+14)) ELSE ''
                          END AS ship_to_addr3


                          Travis-

                          ----- Original Message -----

                          From: "Paul Livengood" <p_livengood@...>
                          To: SQLQueriesNoCode@yahoogroups.com
                          Sent: Wednesday, June 1, 2011 9:40:27 AM
                          Subject: Re: [SQLQueriesNoCode] Elegant technique?

                          its not going to be pretty no matter what because we will ahve to hard
                          code field names and lenghs.
                          Can you supply all the possible field names and do they always come in a
                          specific order (or are they mixed up)?


                          From: Travis Truax <travis.truax@...>
                          To: SQLQueriesNoCode@yahoogroups.com
                          Sent: Wednesday, June 1, 2011 8:23 AM
                          Subject: Re: [SQLQueriesNoCode] Elegant technique?




                          Hi Paul,
                          Yes, it's on a MSSQL2008 box. In the cursor-based routine, I've just got
                          a borderline illegible select statement filled with the following:

                          SELECT
                          SUBSTRING(SOP_COMMENT,PATINDEX('%XORDER%',SOP_COMMENT)+7,9) AS
                          vendor_order_no ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER
                          NBR%',SOP_COMMENT)+12,13) AS member_no
                          ,SUBSTRING(SOP_COMMENT,PATINDEX('%XMEMBER
                          NAME%',SOP_COMMENT)+12,PATINDEX('%XSHIP TO
                          NAME%',SOP_COMMENT)-(PATINDEX('%XMEMBER NAME%',SOP_COMMENT)+12)) AS
                          member_name ,SUBSTRING(SOP_COMMENT,PATINDEX('%XSHIP TO
                          NAME%',SOP_COMMENT)+13,PATINDEX('%XSHIP TO
                          ADDR1%',SOP_COMMENT)-(PATINDEX('%XSHIP TO NAME%',SOP_COMMENT)+13)) AS
                          ship_to_name ......
                          FROM erp_utilities.dbo.sop_order_header_comments_view
                          WHERE ORDER_NUMBER = @sales_order_number AND
                          PATINDEX('%XCLUB%',SOP_COMMENT) > 0

                          Thanks, I hadn't realized that you could use the XML PATH clause to get
                          around the ordered inline views issue.

                          Travis-

                          ----- Original Message -----

                          From: "Paul Livengood" <p_livengood@...>
                          To: SQLQueriesNoCode@yahoogroups.com
                          Sent: Tuesday, May 31, 2011 4:35:25 PM
                          Subject: Re: [SQLQueriesNoCode] Elegant technique?

                          Assuming SQL 2005 or later then concatenating all the ocmment lines for
                          each order is simple (see example at end), but grabing the substrings
                          out between the X column identifiers will be difficult because there is
                          no logic to it. X is a HORRIBLE deliminator because it can be used in
                          normal words.

                          Concatenate example
                          SELECT w1.PO,
                          (SELECT '' + w2.Comment
                          FROM {Table Name} w2
                          WHERE w1.PO = w2.PO
                          ORDER BY PO, ID
                          FOR XML PATH(''))
                          FROM {Table Name} w1
                          GROUP BY PO

                          If you can supply more information on how you are spliting out the
                          xColumn names I may be able to help make this set based instead of
                          cursor based.

                          HTH
                          Paul

                          From: Travis Truax <travis.truax@...>
                          To: SQLQueriesNoCode@yahoogroups.com
                          Sent: Tuesday, May 31, 2011 1:43 PM
                          Subject: [SQLQueriesNoCode] Elegant technique?

                          The order information we get from a certain .com retailer comes to us
                          via EDI as a single merged string, chopped up into rows of data that are
                          limited to something like 64 characters, encoded with field names
                          identified by a preceding "X". Like so:

                          po id comment
                          1234567890 1 XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff XST
                          1234567890 2 ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR
                          1234567890 3 Y US

                          It's really ugly, but I have to deal with it - it's not going to change.

                          This is text processing that many folks would prefer to handle before it
                          gets pushed into the DB, but we prefer to get it in the DB and then
                          process it for other reasons.

                          We need to concatenate all the comment lines for each order, sequenced
                          by id, and then grab substrings out between the "X" column identifiers.

                          I already have a kludgy cursor-based method for this, but I was
                          wondering if there were any ways to do this that were more efficient.
                          Like some way to concatenate all rows within a group or something
                          similar. Even if that were possible, the rows must also be concatenated
                          in proper sequence.
                          I don't think SQL was really designed for this type of processing, but I
                          thought I'd ask... sometimes you can do surprisingly well with the wrong
                          tool for the job, and sometimes you just injure yourself. ;)

                          Thanks,
                          Travis-

                          [Non-text portions of this message have been removed]

                          [Non-text portions of this message have been removed]

                          ------------------------------------

                          Yahoo! Groups Links

                          [Non-text portions of this message have been removed]




                          [Non-text portions of this message have been removed]



                          ------------------------------------

                          Yahoo! Groups Links






                          [Non-text portions of this message have been removed]



                          ------------------------------------

                          Yahoo! Groups Links







                          [Non-text portions of this message have been removed]



                          ------------------------------------

                          Yahoo! Groups Links






                          [Non-text portions of this message have been removed]
                        • Arnie Rowland
                          Travis, I think that you will find the below to be a far more efficient (and faster) method to concatenate the strings and then parse out the values. The query
                          Message 12 of 17 , Jun 1, 2011
                          • 0 Attachment
                            Travis,

                            I think that you will find the below to be a far more efficient (and
                            faster) method to concatenate the strings and then parse out the values.
                            The query returns a EAV (Name-Value pair) table. By creating a ColNames
                            table, you will not have to hard code in order to parse, and you can
                            easily add additional column names when they show up.

                            Query Results:

                            NAME VALUE
                            PURCHASE ORDER 1234567890
                            SHIP TO ADDR2 3500 Amiel Way
                            CITY Flagstaff
                            SHIP TO ZIP-CODE 44286
                            SHIP TO COUNTRY US

                            Regards,

                            Arnie Rowland, MVP (SQL Server)

                            "You cannot do a kindness too soon because you never know how soon it
                            will be too late."
                            -Ralph Waldo Emerson


                            USE tempdb;
                            GO

                            -- This FUNCTION parses the concatenated string
                            CREATE FUNCTION dbo.Split
                            ( @Delimiter varchar(5),
                            @List varchar(8000)
                            )
                            RETURNS @TableOfValues table
                            ( RowID smallint IDENTITY(1,1),
                            [Value] varchar(50)
                            )
                            AS
                            BEGIN
                            DECLARE @LenString int
                            WHILE len( @List ) > 0
                            BEGIN
                            SELECT @LenString =
                            (CASE charindex( @Delimiter, @List )
                            WHEN 0 THEN len( @List )
                            ELSE ( charindex( @Delimiter, @List ) -1 )
                            END
                            )
                            INSERT INTO @TableOfValues
                            SELECT substring( @List, 1, @LenString )
                            SELECT @List =
                            (CASE ( len( @List ) - @LenString )
                            WHEN 0 THEN ''
                            ELSE right( @List, len( @List ) - @LenString - 1 )
                            END
                            )
                            END
                            RETURN
                            END;
                            GO

                            -- This is your test data table
                            CREATE TABLE TestTable
                            ( PO int,
                            ID int,
                            Comment varchar(50)
                            );
                            GO

                            -- The ColumnNames table precludes hard-coding
                            CREATE TABLE ColNames
                            ( ColName varchar(50));
                            GO

                            -- The ColumnNames table precludes hard-coding, based on your supplied
                            data
                            INSERT INTO ColNames
                            VALUES
                            ( 'PURCHASE ORDER' ),
                            ( 'CITY' ),
                            ( 'CLUB' ),
                            ( 'ORDER' ),
                            ( 'MEMBER NBR' ),
                            ( 'MEMBER NAME' ),
                            ( 'SHIP TO NAME' ),
                            ( 'SHIP TO ADDR1' ),
                            ( 'SHIP TO ADDR2' ),
                            ( 'SHIP TO ADDR3' ),
                            ( 'SHIP TO CITY' ),
                            ( 'SHIP TO STATE' ),
                            ( 'SHIP TO ZIP-CODE' ),
                            ( 'SHIP TO PHONE' ),
                            ( 'SHIP TO COUNTRY' )

                            -- Supplied data to concatenate and parse
                            INSERT INTO TestTable
                            VALUES
                            (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff
                            XST'),
                            (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR'),
                            (1234567890, 3, 'Y US');

                            -- Query to create Name/Value pairs from the string
                            SELECT
                            c.ColName AS 'NAME',
                            replace( t4.VALUE, (c.ColName + ' '), '' ) AS 'VALUE'
                            FROM dbo.Split( ',', (SELECT
                            replace( ('Purchase Order ' + cast(PO AS
                            varchar(20)) + ',' + Comment), ' X', ',') AS 'Comment'
                            FROM ( SELECT
                            t1.PO,
                            Comment = substring((SELECT ( '' +
                            Comment )
                            FROM TestTable t2
                            WHERE t1.PO = t2.PO
                            ORDER BY
                            PO,
                            ID
                            FOR XML PATH( '' )
                            ), 2, 1000 ) FROM
                            TestTable t1
                            GROUP BY PO
                            ) t3 )) t4
                            JOIN ColNames c
                            ON t4.Value LIKE (c.ColName + '%');

                            -- Clean Up
                            DROP TABLE TestTable;
                            DROP TABLE ColNames;
                            DROP FUNCTION dbo.SPLIT;


                            [Non-text portions of this message have been removed]
                          • Arnie Rowland
                            (Darn funky formatting...) Travis, I think that you will find the below to be a far more efficient (and faster) method to concatenate the strings and then
                            Message 13 of 17 , Jun 1, 2011
                            • 0 Attachment
                              (Darn funky formatting...)

                              Travis,

                              I think that you will find the below to be a far more efficient (and
                              faster) method to concatenate the strings and then parse out the values.
                              The query returns a EAV (Name-Value pair) table. By creating a ColNames
                              table, you will not have to hard code in order to parse, and you can
                              easily add additional column names when they show up.

                              Query Results:

                              NAME VALUE
                              PURCHASE ORDER 1234567890
                              SHIP TO ADDR2 3500 Amiel Way
                              CITY Flagstaff
                              SHIP TO ZIP-CODE 44286
                              SHIP TO COUNTRY US


                              Regards,

                              Arnie Rowland, MVP (SQL Server)

                              "You cannot do a kindness too soon because you never know how soon it
                              will be too late."
                              -Ralph Waldo Emerson


                              USE tempdb;
                              GO

                              -- This FUNCTION parses the concatenated string
                              CREATE FUNCTION dbo.Split
                              ( @Delimiter varchar(5),
                              @List varchar(8000)
                              )
                              RETURNS @TableOfValues table
                              ( RowID smallint IDENTITY(1,1),
                              [Value] varchar(50)
                              )
                              AS
                              BEGIN
                              DECLARE @LenString int
                              WHILE len( @List ) > 0
                              BEGIN
                              SELECT @LenString =
                              (CASE charindex( @Delimiter, @List )
                              WHEN 0 THEN len( @List )
                              ELSE ( charindex( @Delimiter, @List ) -1 )
                              END
                              )
                              INSERT INTO @TableOfValues
                              SELECT substring( @List, 1, @LenString )
                              SELECT @List =
                              (CASE ( len( @List ) - @LenString )
                              WHEN 0 THEN ''
                              ELSE right( @List, len( @List ) - @LenString - 1 )
                              END
                              )
                              END
                              RETURN
                              END;
                              GO

                              -- This is your test data table
                              CREATE TABLE TestTable
                              ( PO int,
                              ID int,
                              Comment varchar(50)
                              );
                              GO

                              -- The ColumnNames table precludes hard-coding
                              CREATE TABLE ColNames
                              ( ColName varchar(50));
                              GO

                              -- The ColumnNames table precludes hard-coding, based on your supplied
                              data
                              INSERT INTO ColNames
                              VALUES
                              ( 'PURCHASE ORDER' ),
                              ( 'CITY' ),
                              ( 'CLUB' ),
                              ( 'ORDER' ),
                              ( 'MEMBER NBR' ),
                              ( 'MEMBER NAME' ),
                              ( 'SHIP TO NAME' ),
                              ( 'SHIP TO ADDR1' ),
                              ( 'SHIP TO ADDR2' ),
                              ( 'SHIP TO ADDR3' ),
                              ( 'SHIP TO CITY' ),
                              ( 'SHIP TO STATE' ),
                              ( 'SHIP TO ZIP-CODE' ),
                              ( 'SHIP TO PHONE' ),
                              ( 'SHIP TO COUNTRY' )

                              -- Supplied data to concatenate and parse
                              INSERT INTO TestTable
                              VALUES
                              (1234567890, 1, 'XSHIP TO ADDR2 3500 Amiel Way XCITY Flagstaff
                              XST'),
                              (1234567890, 2, 'ATE AZ XSHIP TO ZIP-CODE 44286 XSHIP TO COUNTR'),
                              (1234567890, 3, 'Y US');

                              -- Query to create Name/Value pairs from the string
                              SELECT
                              c.ColName AS 'NAME',
                              replace( t4.VALUE, (c.ColName + ' '), '' ) AS 'VALUE'
                              FROM dbo.Split( ',', (SELECT
                              replace( ('Purchase Order ' + cast(PO AS
                              varchar(20)) + ',' + Comment), ' X', ',') AS 'Comment'
                              FROM ( SELECT
                              t1.PO,
                              Comment = substring((SELECT ( '' +
                              Comment )
                              FROM TestTable t2
                              WHERE t1.PO = t2.PO
                              ORDER BY
                              PO,
                              ID
                              FOR XML PATH( '' )
                              ), 2, 1000 ) FROM
                              TestTable t1
                              GROUP BY PO
                              ) t3 )) t4
                              JOIN ColNames c
                              ON t4.Value LIKE (c.ColName + '%');

                              -- Clean Up
                              DROP TABLE TestTable;
                              DROP TABLE ColNames;
                              DROP FUNCTION dbo.SPLIT;


                              [Non-text portions of this message have been removed]
                            Your message has been successfully submitted and would be delivered to recipients shortly.