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

rchowdhury <rchowdhury(at)alumni(dot)upenn(dot)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°