Emanuel Calvo wrote:
2011/5/19 Adarsh Sharma <adarsh.sha...@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;
Thanks
