From: | Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Convert data into horizontal from vertical form |
Date: | 2011-05-19 10:15:33 |
Message-ID: | 4DD4EDC5.8070402@orkash.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Shafi DBA | 2011-05-19 10:53:56 | " FATAL: no pg_hba.conf entry for host" messages... |
Previous Message | Adarsh Sharma | 2011-05-19 09:45:43 | Re: How to do this ? |