CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedurescursor
el ninho
el ninho

Reputation: 4233

Cursor in stored procedure

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.

Upvotes: 0

Views: 1809

Answers (2)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

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

Upvotes: 2

Volodymyr
Volodymyr

Reputation: 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

Upvotes: -1

Related Questions