Re: Convert data into horizontal from vertical form - Mailing list pgsql-general
From | Adarsh Sharma |
---|---|
Subject | Re: Convert data into horizontal from vertical form |
Date | |
Msg-id | 4DD65768.8020601@orkash.com Whole thread Raw |
In response to | Re: Convert data into horizontal from vertical form (Emanuel Calvo <postgres.arg@gmail.com>) |
List | pgsql-general |
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
pgsql-general by date: