Cursor in stored procedure

Question

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 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 (can't do id manually).

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, to get the output I need. I'm sure it can be done with cursor(s), but I can get to it.

Thanks in advance for help.

possible duplicate of [How to Comma separate multiple rows obtained from a SQL Query](http://stackoverflow.com/questions/5536754/how-to-comma-separate-multiple-rows-obtained-from-a-sql-query) — Mikael Eriksson, Oct 24 '11 at 07:54

score 2 · Accepted Answer · answered Oct 24 '11 at 12:36

If you are using SQL 2008, the following will work without using a cursor:

DECLARE @t TABLE
    (
      id INT
    , number INT
    , VALUE INT
    )

INSERT  INTO @t
        ( id, number, VALUE )
VALUES  ( 1, 300, 233 ),
        ( 2, 343, 434 ),
        ( 2, 565, 655 ),
        ( 3, 562, 343 ),
        ( 1, 434, 232 ),
        ( 3, 232, 444 ),
        ( 3, 458, 232 )


SELECT  DISTINCT ID
      , STUFF(( SELECT  ',' + CONVERT(VARCHAR(10), number) + ':'
                        + CONVERT(VARCHAR(10), VALUE)
                FROM    @t i
                WHERE   t.ID = i.ID
              FOR
                XML PATH('')
              ), 1, 1, '') AS [number:value]
FROM    @t t

score -1 · Answer 2 · edited Oct 24 '11 at 08:11

-1

GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @Number varchar(50);
DECLARE @Value varchar(50);

DECLARE number_cursor CURSOR FOR
select  Number, Value FROM [table_name] for update of Numbervalue

OPEN number_cursor;

FETCH NEXT FROM number_cursor
INTO @Number, @Value;

WHILE @@FETCH_STATUS = 0
BEGIN   
    UPDATE [table_name]
    SET Numbervalue ='@Number'+'@Value' where current of number_cursor    
   FETCH NEXT FROM number_cursor
   INTO @Namber,@Value;
END

CLOSE number_cursor;
DEALLOCATE number_cursor;
GO

edited Oct 24 '11 at 08:11

marc_s

732,580
175
1,330
1,459

answered Oct 24 '11 at 08:06

Volodymyr

1

2

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Oct 24 '11 at 08:11
What is Numbervalue there? And if I wasn't clear enough, it is MS SQL. Thanks. – el ninho Oct 24 '11 at 09:20
JUst becasue it can be done in cursor and the OP requested a cursor, doesn;t mean it is the best choice for an anaswer. This task does not require a cursor and one should not be used. – HLGEM Oct 24 '11 at 17:26

Cursor in stored procedure

2 Answers2