recursive sql query - concatenation

So I need to write a stored procedure and I’m working on the SQL query right now, this is what I need and I wondered if anyone had any SQL experience.
I have one table with 2 rows to compare like so

Code:

question |  score
---------- | --------
100          | 1
100          | 2
100          | 3
120          | 1
120          | 1
120          | 2
130          | 2
130          | 2
130          |3

okay, so there are a few questions and their scores, there are other fields but this is what I need to work with.

I need to output something like this

Code:

100 | 1,2,3
120 | 1,1,2
130 | 2,2,3

So I need to take the info in the score row and line it up with the question id.
Anyone want to take a stab

 

Not an option, also that's the dirty way of doing it..lol
I about had it yesterday but it was displaying like I wanted.

This is close
http://stackoverflow.com/questions/1757370/recursive-same-table-query-in-sql-server-2008?rq=1

 

lol, I just wrote my first stored procedure for MySQL:

Code:

delimiter //
 
create function gather_scores( k int ) returns text
begin
  -- declare variables
  declare scores text default '';
  declare single_score int default -1;
  declare last_score int;
  -- named loop for getting entries
next_item:
  loop
    -- set last score
    set last_score = single_score;
    -- get next line of data
    select score into single_score from test where question = k and score > last_score limit 1;
    if single_score = last_score then
      -- no result (empty line)
      leave next_item;
    end if;
    -- add comma if scores is non-empty
    if scores != '' then
      set scores = concat( scores, ',' );
    end if;
    -- add current score value
    set scores = concat( scores, single_score );
  end loop next_item;
  return scores;
end//
 
delimiter ;

To call it, do a "select gather_scores(100);" for instance.

However, for 120 and 130 it won't work correctly. I found no way to gather multiple lines with identical values in the stored procedure language of MySQL.

EDIT: p.s. also, it's an iteration, not a recursion, but perhaps I'll post one later when I have more time.


HTH!

 

Ha, I found a solution (still an iteration, but it works for 120 and 130 too) :

Code:

delimiter //
 
create function gather_scores( k int ) returns text
begin
    -- declare variables
    declare scores text default '';
    declare single_score int default -1;
    declare last_score int;
    declare num_same int;
    -- named loop for getting entries
next_item:
    loop
      -- set last score
      set last_score = single_score;
      -- get next line of data
      select score into single_score from test where question = k and score > last_score limit 1;
      if single_score = last_score then
        -- no result (empty line)
        leave next_item;
      end if;
      -- count identical lines
      set num_same = 0;
      select count(*) into num_same from test where question = k and score = single_score;
      -- counting loop
count_loop:
      while num_same > 0 do
        -- count down
        set num_same = num_same - 1;
        -- add comma if scores is non-empty
        if scores != '' then
          set scores = concat( scores, ',' );
        end if;
        -- add current score value
        set scores = concat( scores, single_score );
      end while count_loop;
    end loop next_item;
    return scores;
end//
 
delimiter ;

 

Here's a tail recursive version (something I learnt from Erlang! ) :

Code:

delimiter //

create procedure gather_scores_recursively( k int, vmin int, inout scores text ) 
body:
begin
    declare single_score int default -1;
    declare num_same int default 0;
    -- get next line of data
    select score into single_score from test where question = k and score > vmin limit 1;
    -- if that was empty, return empty string
    if single_score = -1 then 
      leave body;
    end if;
    -- count identical lines
    select count(*) into num_same from test where question = k and score = single_score;
    -- create output string
count_loop:
    while num_same > 0 do
      -- count down
      set num_same = num_same - 1;
      -- add comma if scores is non-empty
      if scores != '' then 
        set scores = concat( scores, ',' ); 
      end if;
      -- add current score value
      set scores = concat( scores, single_score );
    end while count_loop;
    -- tail recursion: concat scores with higher values
    call gather_scores_recursively( k, single_score, scores );
end//

create function gather_scores( k int ) returns text
begin
    declare scores text default '';
    call gather_scores_recursively( k, -1, scores );
    return scores;
end//

delimiter ;
 

Of course, the recursion limit for the session must be set, like so:

Code:

mysql> set max_sp_recursion_depth=100;
Query OK, 0 rows affected (0.00 sec)

The function gather_scores() is a convenience function that calls the recursive procedure. Unfortunately, MySQL doesn't support recursive functions, only procedures, and function/procedure arguments cannot have a default value.

Of course, since the recursion depth is limited by a setting, the recursive version is less powerful than the iterative version posted before.

 

I may have worded this wrong, I believe what I need is a concatenation string.

 

Did my solution not work for you? (not sure if MySQL procedures work on MSSQL, but theoretically, they should)

You can call it with:

Code:

select distinct question, gather_scores( question ) as scores from test;

 

I got it working, well almost.
I need to join it with one other table but the command I was missing was the PIVOT command, which allowed me to display the data like I needed.

Thanks for the help everyone

 

Jeez, I don't envy you. I just tried to find that in the MS-SQL Documentation ( and I did ), and I found that while in earlier years, Microsoft often aced when it came to developer documentation, in the past years, it all has gone downhill. Never seen such a badly structured set of docs (except from Apple or Sun perhaps) ... it won't take long until Microsoft has lost its last developer ... the migration away from the old natively coded help browsers towards web based help was a terrible move, IMO ... you know, I normally did a lot of Windows API programming, but that dog of a documentation isn't usable anymore either ... I'll probably never touch Windows programming again ... (unless I get chained to a computer someday by an employer ).

And while I do hate Linux sometimes for some of its quirks, most of its documentation is easily accessible and readable. For me, as someone who always looks in the documentation first before using something, MS has become a nightmare ...

If you look at the "helpful" ratings of MS's documentation, you'll see that only few people manage to use that pile of junk and find something useful in it.

/rant

 

My code looks very similar with a few joins in place as well.