Re: Convert data into horizontal from vertical form

Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> · Fri, 20 May 2011 17:28:32 +0530

Emanuel Calvo wrote:

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

    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