Обсуждение: Use for loop in stored procedure to join query results

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
that...
1. SELECT all distinct "id"s
2. FOR LOOP to iterate over each "id", SELECT probe, value  WHERE
id=<current id>
3. JOIN resulting table to table from previous iteration of the FOR loop

Unfortunately, I am not sure if this is a good way to do this.  And I am not
familiar with stored procedure syntax.  Can anyone help with this?

Thanks,
RC


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Use-for-loop-in-stored-procedure-to-join-query-results-tp3286416p3286416.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

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°

That worked perfectly.  Thanks Andreas!
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Use-for-loop-in-stored-procedure-to-join-query-results-tp3286416p3286600.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.