Skip site navigation (1) Skip section navigation (2)

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

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Use for loop in stored procedure to join query results
Date: 2010-11-30 17:50:33
Message-ID: 20101130175033.GA28215@tux (view raw or flat)
Thread:
Lists: pgsql-novice
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°

In response to

Responses

pgsql-novice by date

Next:From: rchowdhuryDate: 2010-11-30 19:26:07
Subject: Re: Use for loop in stored procedure to join query results
Previous:From: MickDate: 2010-11-30 17:33:58
Subject: Attempting backup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group