Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Problem with cursors

    I have table like this:

    id number value
    1 300 233
    2 343 434
    2 565 655
    3 562 343
    1 434 232
    3 232 444
    3 458 232

    It have to be

    id number:value, number:value...
    1 300:233, 434:232
    2 343:434, 565:655
    ...

    and so on

    Basically, I have to write stored procedure to merge 2nd and 3rd column and group for every ID.

    What I did is CAST, and I got "merged" 2nd and 3rd column, and now I need to group id by id, for unknown number of ids (cant do id manualy).

    So, instead of original 3-column table, I made new one with 2 rows

    id number:value
    1 300:233
    2 343:434
    2 565:655
    3 562:343
    1 434:232
    3 232:444
    3 458:232

    Just need somehow to group it. I'm sure it can be done with cursor(s), but I can get to it.
    Thanks in advance for help.
    Last edited by el ninho; 10-23-11 at 22:07.

  2. #2
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Avoid cursors wherever possible - use a function to generate a string based on the ID...

    Code:
    CREATE function  [dbo].[get_value_list_thing] (@ID Int)
    RETURNS nvarchar(512)
    AS
    Begin
    	Declare @v_ValueList nvarchar(512) = Null
    	
    	Select	@v_ValueList =  Case
    					When @v_ValueList Is Null Then ''
    					Else @v_ValueList + ', '
    				End + cast(Number as nvarchar) + ':'+cast(Value as nvarchar)
    	From	YourTable
    	Where	ID = @ID 
    	ORDER by Number
    
    	RETURN	@v_ValueList 
    
    END
    GO
    
    select distinct id,dbo.get_value_list_thing(ID) from yourtable

  3. #3
    Join Date
    Sep 2011
    Posts
    71
    take a look to below idea and apply it on your Code

    he trick is to introduce a table of values (named, in the example below, MyTableOfIntegers) which contains all the integer values between 1 and (at least) some value (in the case at hand, that would be the biggest possible Quantity value from OrderTransaction table).

    INSERT INTO #OrderDelivery ([OrderTransactionId], [MediaTypeId])
    SELECT OT.OrderTransactionId, P.MediaTypeId
    FROM #OrderTransaction OT WITH (NOLOCK)
    INNER JOIN #Product P WITH (NOLOCK)
    ON OT.ProductId = P.ProductId
    JOIN MyTableOfIntegers I ON I.Num <= OT.Quantity
    --WHERE some optional conditions

    Essentially the extra JOIN on MyTableOfIntegers, produces as many duplicate rows as OT.Quantity, and that seems to be what the purpose of the cursor was: to insert that many duplicated rows in the OrderDelivery table.

    I didn't check the rest of the logic with the temporary tables and all (I'm assuming these are temp tables for the purpose of checking the logic rather than being part of the process proper), but it seems that the above is the type of construct needed to express the needed logic in declarative fashion only, without any cursor or even any loop.
    For further details goto
    performance - SQL Server: Improve PROCEDURE without using CURSOR - Stack Overflow

Posting Permissions

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