postgresql query

Which version of Postgresql are you using?
However, you may use string_agg like below if its available in your version:

\d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
amt | integer |

select * from t1;
i | amt
---+-----
1 | 20
1 | 30
2 | 30
(3 rows)

select i, string_agg(amt::text,',') as c from t1 group by i;;
i | c
---+-------
1 | 20,30
2 | 30
(2 rows)

Have a good day.

On Wed, Jun 19, 2013 at 6:51 AM, Jashaswee <sweet.rinky72@gmail.com> wrote:

2013/6/19 Jashaswee <sweet.rinky72@gmail.com>

The column value looks like this:

Quantity
2000
-1000

both the quantity values are of a same product.but i want these in a
single
line.
so what i want is a result set that looks like:
In quantity Out quantity
----------- ------------
2000 -1000

How do we decide wether 2000 or -1000 refers to the “In quantity”?
Can you show a complete table's structure, please?

--
Victor Y. Yegorov

Jashaswee wrote

I have numeric values in a numeric column.the column has two parts.i want
to split in 2 differnet column .
The column value looks like this:

Quantity
2000
-1000

both the quantity values are of a same product.but i want these in a
single line.
so what i want is a result set that looks like:
In quantity Out quantity
----------- ------------
2000 -1000

how can i get this in a select statement ?

I presume this is a debit/credit situation.

Basically you use a CASE expression to put the amount into the correct
column depending on whether it is greater or less than zero. The rest of
the query is simply a matter of what kind of detail you want.

WITH make_debit_credit_columns_for_each_record AS (
SELECT ...
, CASE WHEN amt >= 0 THEN amt ELSE 0.00 END AS debit
, CASE WHEN amt < 0 THEN amt ELSE 0.00 END AS credit
FROM source_table
)
SELECT ..., SUM(debit) AS total_debit, SUM(credit) AS total_credit
FROM make_debit_credit_columns_for_each_record
GROUP BY ...

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760003.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

i am using postgresql 8.4 version..but this doesn't support the function

--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760589.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

i have tried in that way but its showing that the debit column doesn't exist

--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760593.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jashaswee wrote

i have tried in that way but its showing that the debit column doesn't
exist

So show us what exactly it is that you tried and maybe someone can tell you
what is wrong.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760666.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general