Re: Use for loop in stored procedure to join query results

rchowdhury <rchowdhury@alumni.upenn.edu> wrote:

>
> Hello,
>
> I have a table similar to the following:
>
> id   probe   value
> 1   asdf   10
> 1   qwer   20
> 1   zxcv   30
> 2   asdf   40
> 2   qwer   50
> 2   zxcv   60
>
> I would like to create a stored procedure or function that will make a view
> with the data as follows:
>
>            1     2
> asdf    10   40
> qwer   20   50
> zxcv   30   60
>
> Does anyone know how to do this?  I am attempting to make a stored procedure

test=*# select * from foo;
 id | probe | value
----+-------+-------
  1 | asdf  |    10
  1 | qwer  |    20
  1 | zxcv  |    30
  2 | asdf  |    40
  2 | qwer  |    50
  2 | zxcv  |    60
(6 Zeilen)

Zeit: 0,275 ms
test=*# select   probe,
                 sum(case when id=1 then value else null end) as "1",
                 sum(case when id=2 then value else null end) as "2"
        from     foo
        group by 1
        order by 1;
 probe | 1  | 2
-------+----+----
 asdf  | 10 | 40
 qwer  | 20 | 50
 zxcv  | 30 | 60
(3 Zeilen)



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°