Re: Convert data into horizontal from vertical form

Emanuel Calvo wrote:
> 2011/5/19 Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)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
>
>