Professional Web Applications Themes

Without cursor - Microsoft SQL / MS SQL Server

Hello I have a child table like this ID, Item ---------- 1 12 1 100 1 250 2 5 2 60 3 600 3 654 3 852 Now i want to collect all child values of one parent in one string, like for 1 = "12,100,250" I did that with cursor, but it is kinda slow. It is no problem to create this on client with recordset, but i wanna to perform this task in SP. Any idea? bye Spike...

  1. #1

    Default Without cursor

    Hello

    I have a child table like this

    ID, Item
    ----------
    1 12
    1 100
    1 250
    2 5
    2 60
    3 600
    3 654
    3 852

    Now i want to collect all child values of one parent in one string, like for
    1 = "12,100,250"
    I did that with cursor, but it is kinda slow.
    It is no problem to create this on client with recordset, but i wanna to
    perform this task in SP.

    Any idea?

    bye

    Spike



    news.siol.net Guest

  2. #2

    Default Re: Without cursor

    Here is one solution that I can think of:
    ==============
    CREATE TABLE samp_test
    (
    tableID INT,
    Item INT
    )
    INSERT INTO samp_test VALUES (1, 12)
    INSERT INTO samp_test VALUES (1, 100)
    INSERT INTO samp_test VALUES (1, 250)
    INSERT INTO samp_test VALUES (2, 5)
    INSERT INTO samp_test VALUES (2, 60)

    CREATE FUNCTION combineResults (tableID INT) RETURNS VARCHAR(8000) AS
    BEGIN
    DECLARE var VARCHAR(8000)
    SET var = ''
    SELECT var = var + CAST(item AS VARCHAR(10)) + ', ' FROM samp_test
    WHERE tableID = tableID
    RETURN (SUBSTRING(var, 1, DATALENGTH(var) - 2))
    END

    SELECT tableID, Results = dbo.combineResults (tableID) FROM samp_test GROUP
    BY tableID
    ==============
    Ideally these kind of string operations are not recommended in SQL Server
    and is best performed in client applications.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    [url]http://www32.brinkster.com/srisamp[/url]

    "news.siol.net" <damjan.murkosiol.net> wrote in message
    news:mwbPa.157$2B6.31115news.siol.net...
    > Hello
    >
    > I have a child table like this
    >
    > ID, Item
    > ----------
    > 1 12
    > 1 100
    > 1 250
    > 2 5
    > 2 60
    > 3 600
    > 3 654
    > 3 852
    >
    > Now i want to collect all child values of one parent in one string, like
    for
    > 1 = "12,100,250"
    > I did that with cursor, but it is kinda slow.
    > It is no problem to create this on client with recordset, but i wanna to
    > perform this task in SP.
    >
    > Any idea?
    >
    > bye
    >
    > Spike
    >
    >
    >

    SriSamp Guest

  3. #3

    Default Re: Without cursor

    "news.siol.net" is thinking about server side concatenation:
    > Now i want to collect all child values of one parent in one string, like
    for
    > 1 = "12,100,250"
    > I did that with cursor, but it is kinda slow.
    > It is no problem to create this on client with recordset, but i wanna to
    > perform this task in SP.
    > Any idea?
    >>Ideally these kind of string operations are not recommended in SQL Server
    >>and is best performed in client applications.
    *Ideally*!....where do you work :~)

    This type of concatenation over rows is
    trivial (easy:) with the RAC utility for S2k.
    No coding required.
    Check out:
    [url]http://www.rac4sql.net/onlinehelp.asp?topic=236[/url]


    RAC v2.2 and QALite released.
    [url]www.rac4sql.net[/url]




    Groucho Guest

  4. #4

    Default Re: Without cursor




    "SriSamp" <ssampathsct.co.in> wrote in message
    news:OhqH2uuRDHA.2148TK2MSFTNGP12.phx.gbl...
    > I'm not sure how RAC4SQL does it. The link given below does not work, as
    it
    > says "file not found".
    Webhost4life is in process of moving our data from one server to the next.
    The site will be down until then. :(


    --
    -oj
    [url]http://www.rac4sql.net[/url]


    oj Guest

  5. #5

    Default Re: Without cursor

    "SriSamp" wrote in message
    > I'm not sure how RAC4SQL does it.
    That's the point of a utility.It's like Nike:just go ahead
    and easily do 'it' without concern with the 'how' :~).

    RAC v2.2 and QALite released.
    [url]www.rac4sql.net[/url]







    Groucho Guest

Similar Threads

  1. Cursor bug?
    By Tom Lane in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 17th, 09:25 AM
  2. cursor is gone
    By csharv in forum Macromedia Director Basics
    Replies: 2
    Last Post: April 15th, 12:47 PM
  3. look at the cursor
    By Seph webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 2
    Last Post: December 18th, 01:56 PM
  4. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  5. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139