Re: [GENERAL] Convert data into horizontal from vertical form
Emanuel Calvo wrote:
2011/5/19 Adarsh Sharma :
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 a1b1
3 a2b2
3 a3b3
3 a4b4
3 a5b5
1 c1d1
1 c2d2
3 a1e1
3 a2 e2
3 a3e3
3 a4e4
3 a5e5
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 a1a2 a3 a4 a5
3 b1b2b3 b4 b5
3e1e2 e3e4
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 1 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,col3and 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;
Thanks
Re: [GENERAL] Convert data into horizontal from vertical form
2011/5/19 Adarsh Sharma : > 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 1 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 On 19 May 2011 11:15, Adarsh Sharma wrote: > 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 1 of rows & 100 of c_id's. > > > Please let me know if it is possible or any information is required. > > > Thanks > > > > create function user_news_new(text) returns void as $$ > declare > name text; > cat_name alias for $1; > begin > CREATE TEMPORARY TABLE temptest(category_id INTEGER,category_name text); > /* create a temp table to hold all the dynamic schemas*/ > for name in select label_name from category_new where category_id = (select > category_id from category where category_name=cat_name) loop > execute 'alter table temptest add column ' || name || ' text'; > end loop; > end; > $$ language plpgsql; > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Convert data into horizontal from vertical form
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 a1b1 3 a2b2 3 a3b3 3 a4b4 3 a5b5 1 c1d1 1 c2d2 3 a1e1 3 a2 e2 3 a3e3 3 a4e4 3 a5e5 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 a1a2 a3 a4 a5 3 b1b2b3 b4 b5 **3e1e2 e3e4 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 1 of rows & 100 of c_id's. Please let me know if it is possible or any information is required. Thanks create function user_news_new(text) returns void as $$ declare name text; cat_name alias for $1; begin CREATE TEMPORARY TABLE temptest(category_id INTEGER,category_name text);/* create a temp table to hold all the dynamic schemas*/ for name in select label_name from category_new where category_id = (select category_id from category where category_name=cat_name) loop execute 'alter table temptest add column ' || name || ' text'; end loop; end; $$ language plpgsql;
