SQL question - concatenating records without a cursor


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: SQL question - concatenating records without a cursor

  1. #1
    Joseph Van Valen Guest

    SQL question - concatenating records without a cursor


    Hi,

    I have your SqlServer 7 book and it has been a tremendous help! If we ever
    upgrade to the 2000 version, I'll be sure to upgrade the book as well.

    I've been assigned a data project where we need to export some data from
    SqlServer 7 to a full text search engine (The sql server full text search
    engine is not suitable in this case). A large part of this process involves
    concatenating the records of a number of tables into a single string. ("Record1",
    "Record2",... becomes "Record1 Record2..." for each table involved).

    Currently, we have a stored procedure to do the concatenations via a cursor.
    As you can imagine, this process is dreadfully slow.

    Is there was a way to do this using TSQL that does not require cursors that
    is faster?

    Thanks for any insights you can provide.

    Joseph Van Valen


    Share on Google+

  2. #2
    Rob Vieira Guest

    Re: SQL question - concatenating records without a cursor

    Hi John,

    It's possible that you may be able to achieve the result you're after by
    joining the table back to itself, but I would need more specific information
    on the structure of the table and the logic behind the concatenation.

    If you can do it as a join, it would wind up looking something like:

    SELECT a.MyColumn + ' ' + b.MyColumn
    FROM MyTable AS a
    JOIN MyTable AS b
    ON a.<identifier> = b.<some other identifier>
    WHERE......

    Again, figuring this one out all the way would require a little bit more
    information.


    --
    Rob Vieira MCSD, MCT, MCDBA
    www.ProfessionalSQL.com

    "Joseph Van Valen" <vanvalen@att.com> wrote in message
    news:3a6c8f1e$1@news.devx.com...
    >
    > Hi,
    >
    > I have your SqlServer 7 book and it has been a tremendous help! If we ever
    > upgrade to the 2000 version, I'll be sure to upgrade the book as well.
    >
    > I've been assigned a data project where we need to export some data from
    > SqlServer 7 to a full text search engine (The sql server full text search
    > engine is not suitable in this case). A large part of this process

    involves
    > concatenating the records of a number of tables into a single string.

    ("Record1",
    > "Record2",... becomes "Record1 Record2..." for each table involved).
    >
    > Currently, we have a stored procedure to do the concatenations via a

    cursor.
    > As you can imagine, this process is dreadfully slow.
    >
    > Is there was a way to do this using TSQL that does not require cursors

    that
    > is faster?
    >
    > Thanks for any insights you can provide.
    >
    > Joseph Van Valen
    >
    >



    Share on Google+

  3. #3
    Josep hVan Valen Guest

    Re: SQL question - concatenating records without a cursor


    Actually these tables are the many side of a one to many relationship with
    a master table.

    The structure of the related tables is pretty simple in most cases - just
    one link field and a varchar field with some text data.

    Typically

    Create Table MasterItems (
    PKID int not null -- primary key
    Title varchar(100)
    ....
    )

    Create Table Related (
    PKID int not null -- Primary key
    ItemID int -- link to MasterItems
    Txt varchar(100) -- The field to concatenate


    The concatenation is basically to append all related record from a table
    into a single string. So if the are n related records in the related table
    then the result is one string containing the n varchar fields concatenated
    together

    Since the search engine of choice can only deal with "flat" records, we are
    attempting to flatten them by converting the related records to one long
    string as if all of the related records were just one field value.

    So master.rec1 - rel.rec1.txt
    rel.rec2.txt
    rel.rec3.txt
    etc...

    becomes
    master.rec1 - "rel.rec1.txt+rel.rec2.txt+rel.rec3.txt+etc..."

    I hope this is a better explanation of what we're trying to do.

    Thanks for looking at this...

    Joseph Van Valen

    "Rob Vieira" <RobV@nospam.removethis.ProfessionalSQL.com> wrote:
    >Hi John,
    >
    >It's possible that you may be able to achieve the result you're after by
    >joining the table back to itself, but I would need more specific information
    >on the structure of the table and the logic behind the concatenation.
    >
    >If you can do it as a join, it would wind up looking something like:
    >
    >SELECT a.MyColumn + ' ' + b.MyColumn
    >FROM MyTable AS a
    >JOIN MyTable AS b
    > ON a.<identifier> = b.<some other identifier>
    >WHERE......
    >
    >Again, figuring this one out all the way would require a little bit more
    >information.
    >
    >
    >--
    >Rob Vieira MCSD, MCT, MCDBA
    >www.ProfessionalSQL.com
    >
    >"Joseph Van Valen" <vanvalen@att.com> wrote in message
    >news:3a6c8f1e$1@news.devx.com...
    >>
    >> Hi,
    >>
    >> I have your SqlServer 7 book and it has been a tremendous help! If we

    ever
    >> upgrade to the 2000 version, I'll be sure to upgrade the book as well.
    >>
    >> I've been assigned a data project where we need to export some data from
    >> SqlServer 7 to a full text search engine (The sql server full text search
    >> engine is not suitable in this case). A large part of this process

    >involves
    >> concatenating the records of a number of tables into a single string.

    >("Record1",
    >> "Record2",... becomes "Record1 Record2..." for each table involved).
    >>
    >> Currently, we have a stored procedure to do the concatenations via a

    >cursor.
    >> As you can imagine, this process is dreadfully slow.
    >>
    >> Is there was a way to do this using TSQL that does not require cursors

    >that
    >> is faster?
    >>
    >> Thanks for any insights you can provide.
    >>
    >> Joseph Van Valen
    >>
    >>

    >
    >


    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center