Обсуждение: 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 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.
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;
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.
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;
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 <adarsh.sharma@orkash.com> 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 10000 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; >
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:
I am able to understand the 2nd procedure as but have some conflicts with it :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
CREATE OR REPLACE FUNCTION pivoty(query text) RETURNS void AS $pivot$ DECLAREnum_cols int;num_rows int;table_pivoted text;columna text; BEGINDROP 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;
Thanks