Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2012
    Posts
    6

    Unanswered: Need some help in a recursive query

    Hi folks,

    I have a question regarding a recursive query.

    Here is what my table look like:
    EVENT_ID TEXT_FRO TEXT_TO
    55001 05
    55001 05 26
    55001 26 28
    55001 28 27
    55001 27 26
    55001 26 27
    55001 27 28
    55001 28 30
    55001 30 40
    56215 05
    56215 05 85
    56215 05 85

    How can I produce result set like:
    Event ID Movements
    55001 05 26 28 27 26 27 28 30 40
    ........ .....

    Thank you in advance for your help.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by nzsquall View Post
    Hi folks,

    I have a question regarding a recursive query.

    Here is what my table look like:
    EVENT_ID TEXT_FRO TEXT_TO
    55001 05
    55001 05 26
    55001 26 28
    55001 28 27
    55001 27 26
    55001 26 27
    55001 27 28
    55001 28 30
    55001 30 40
    56215 05
    56215 05 85
    56215 05 85

    How can I produce result set like:
    Event ID Movements
    55001 05 26 28 27 26 27 28 30 40
    ........ .....

    Thank you in advance for your help.
    I recursive query (stored procedure) calls itself several times. A self join could also produce these results. Which one does your instructor expect?

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i doubt any instructor seriously expects eight self-joins in a single query

    to me, this doesn't look like an assignment at all

    to the original poster, i would ask why can't you thread all the movements together in your front-end application language?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2012
    Posts
    6
    Hi corncrowe,

    Thanks for your reply.

    I think a stored procedure will be more efficient here. Can you share me some of your ideas?

    Many thanks.

  5. #5
    Join Date
    Oct 2012
    Posts
    6
    Quote Originally Posted by r937 View Post
    i doubt any instructor seriously expects eight self-joins in a single query

    to me, this doesn't look like an assignment at all

    to the original poster, i would ask why can't you thread all the movements together in your front-end application language?
    Hi r937,

    Thanks for your reply. This is a third party application (maybe 10 years old already) so we can't really change the front end nor the database structure.......

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if it's a 10-year-old application, how come this is the first time you've had to string together event movements? how was this being done up till now?

    clearly, you're able to modify the code because otherwise how would you run this new query (or stored procedure)?

    in whatever module you plan to place the call to this new query or stored procedure, just put the movement logic there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2012
    Posts
    6
    Quote Originally Posted by r937 View Post
    if it's a 10-year-old application, how come this is the first time you've had to string together event movements? how was this being done up till now?

    clearly, you're able to modify the code because otherwise how would you run this new query (or stored procedure)?

    in whatever module you plan to place the call to this new query or stored procedure, just put the movement logic there
    This is the first time I am asked to produce a report like that
    Event ID Movements
    55001 05 26 28 27 26 27 28 30 40
    And what I need is the logic to produce this result set.

    The application is old so we can't modify the front end or existing database structure to thread all these movements together. But what I am struggling to do it to write a stored procedure and call it regularly to view the expected results.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am not suggesting that you modify the front end or existing database structure

    where are you going to use the query or stored procedure that you hope to develop? how are you going to call it? where is the call going to come from?

    that's where you put the movement logic

    just write a simple ORDER BY query, loop over the returned rows, and string together the related movements

    very, very basic programming, a ~lot~ simpler than trying to do it with a query or stored procedure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2012
    Posts
    6
    Sorry I am not sure if I understand your questions.

    If I am doing a SP I will be creating the SP in the database, and call it from the database and fetch the result set from that static database table.

    Are you suggesting I use another tool (like Excel) to do the concatenation instead of SP?

  10. #10
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Need some help...

    OK, I'll give it a go. Would the following SQL help?

    Code:
    select
      e.EVENT_ID
     ,(select isnull(TEXT_TO,TEXT_FRO) as "data()"
       from myTable where EVENT_ID = e.EVENT_ID for xml path('')
      ) as Movements
    from
      (select distinct EVENT_ID from myTable) e

  11. #11
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    What's wrong with the answers you got on Stackoverflow?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  12. #12
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by nzsquall View Post
    Hi corncrowe,

    Thanks for your reply.

    I think a stored procedure will be more efficient here. Can you share me some of your ideas?

    Many thanks.
    Look up pivot or crosstab. Should give you an idea how to approach this problem.

    Pivots with Dynamic Columns in SQL Server 2005

  13. #13
    Join Date
    Oct 2012
    Posts
    6
    I posted that question on Stackoverflow after this. The answer there solves my problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •