Re: Convert data into horizontal from vertical form

[Phil Couling <couling@xxxxxxxxx>]

Hi Adarsh

You say you need this to be done dynamically.
I assume that by this you're looking for a way to have 1 query produce
an increasing number of columns as you increase the number of rows in
your table.

This really isn't possible and doesn't fit with the model SQL was designed for.
The concept of tables is that each table represents a set of items of
a single type with a set of known properties (the possible properties
are known before the item itself).
An item is represented by a row and a property is represented by a column.

You are trying to create a query with an unknown set of properties.

If the data must be represented as you've shown then you will need to
get your front end application to transform the data for you.

Regards



On 19 May 2011 11:15, Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> wrote:
> 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;
>

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general