Convert data into horizontal from vertical form

Hi Adarsh

You say you need this to be done dynamically.
I assume that by this you're looking for a way to have 1 query produce
an increasing number of columns as you increase the number of rows in
your table.

This really isn't possible and doesn't fit with the model SQL was designed for.
The concept of tables is that each table represents a set of items of
a single type with a set of known properties (the possible properties
are known before the item itself).
An item is represented by a row and a property is represented by a column.

You are trying to create a query with an unknown set of properties.

If the data must be represented as you've shown then you will need to
get your front end application to transform the data for you.

Regards

2011/5/19 Adarsh Sharma <adarsh.sharma@orkash.com>:

Dear all,

I am not able to insert data into a table in horizontal form.

The data is in below form :

A show a small set of data :-

c_id               f_name           f_value
2                     k1                      v1
2                     k2                      v2
2                     k3                      v3
2                     k4                       v4
3                     a1                        b1
3                     a2                        b2
3                     a3                        b3
3                     a4                        b4
3                     a5                        b5
1                     c1                        d1
1                     c2                        d2
3                     a1                        e1
3                      a2                       e2
3                     a3                        e3
3                     a4                        e4
3                     a5                        e5

Now i want to show the above data in horizontal form as per c_id , fore.g if
a user enters c_id 3  then output is :

c_id               a1            a2             a3           a4           a5
3                     b1            b2            b3           b4         b5
3                    e1            e2              e3            e4
  e5

i.e f_name entries became the columns of the table & f_value become the rows

I research on crosstab function but i don'e think it is useful because we
have to give column names in the command.
I want to show it dynamically . I try to create a procedure & also attach
it.

A user enters only c_id & output is shown fore.g if a user enters c_id 1
then output is

c_id               c1               c2
1                     d1               d2

I show the data in simple way bt there r 10000 of rows & 100 of c_id's.

Please let me know if it is possible or any information is required.

Something like this?

http://wiki.postgresql.org/wiki/Pivot_query

--
--
              Emanuel Calvo
              Helpame.com

Emanuel Calvo wrote:

2011/5/19 Adarsh Sharma <adarsh.sharma@orkash.com>:

Dear all,

I am not able to insert data into a table in horizontal form.

The data is in below form :

A show a small set of data :-

c_id f_name f_value
2 k1 v1
2 k2 v2
2 k3 v3
2 k4 v4
3 a1 b1
3 a2 b2
3 a3 b3
3 a4 b4
3 a5 b5
1 c1 d1
1 c2 d2
3 a1 e1
3 a2 e2
3 a3 e3
3 a4 e4
3 a5 e5

Now i want to show the above data in horizontal form as per c_id , fore.g if
a user enters c_id 3 then output is :

c_id a1 a2 a3 a4 a5
3 b1 b2 b3 b4 b5
3 e1 e2 e3 e4
e5

i.e f_name entries became the columns of the table & f_value become the rows

I research on crosstab function but i don'e think it is useful because we
have to give column names in the command.
I want to show it dynamically . I try to create a procedure & also attach
it.

A user enters only c_id & output is shown fore.g if a user enters c_id 1
then output is

c_id c1 c2
1 d1 d2

I show the data in simple way bt there r 10000 of rows & 100 of c_id's.

Please let me know if it is possible or any information is required.

Something like this?

http://wiki.postgresql.org/wiki/Pivot_query

I am able to understand the 2nd procedure as but have some conflicts
with it :

CREATE OR REPLACE FUNCTION pivoty(query text) RETURNS void AS $pivot$
DECLARE
num_cols int;
num_rows int;
table_pivoted text;
columna text;
BEGIN
DROP TABLE IF EXISTS pivoted;
DROP TABLE IF EXISTS pivot_;

EXECUTE 'CREATE TEMP TABLE pivot_ AS ' || query ;

SELECT count(*) INTO num_cols FROM information_schema.COLUMNS WHERE table_name = 'pivot_';
SELECT count(*) INTO num_rows FROM pivot_;

table_pivoted := 'CREATE TABLE pivoted (';
FOR i IN 1 .. num_rows
LOOP

IF ( i = num_rows )
THEN
SELECT table_pivoted || 'col' || i || ' text ' INTO table_pivoted;
ELSE
SELECT table_pivoted || 'col' || i || ' text ,' INTO table_pivoted;
END IF;
END LOOP;

SELECT table_pivoted || ')' INTO table_pivoted;

EXECUTE table_pivoted;

/******The above if-else condition will create the columns (col1,col2,col3....and so on ) depending upon the rows of original table but I want the column names depend upon f_name column and category_id input by user*/

/* It may be 10,12,11,15 columns**/

FOR columna IN SELECT column_name::Text FROM information_schema.COLUMNS WHERE table_name = 'pivot_'
LOOP
EXECUTE 'INSERT INTO pivoted SELECT ((translate(array_agg(' || columna || ')::text,''{}'',''()'' ))::pivoted).* FROM pivot_';

END LOOP;

/*****How to call the procedure * pivoty(query text)
/****and how it insert data in new table*******/

*END;
$pivot$ LANGUAGE plpgsql;