Re: How to do this ?

Esmin Gracic wrote:
> On Wed, May 18, 2011 at 1:25 PM, Adarsh Sharma
> <adarsh(dot)sharma(at)orkash(dot)com <mailto:adarsh(dot)sharma(at)orkash(dot)com>> wrote:
>
> Dear all,
>
> I explain in the simple terms :
>
> Our application stores data in a format that is not best
> fitted to analyze.
>
> _*Table news
>
> *_category_id Record_id field_name field_value
>
>
>
> 78 21 Village
> adasrpur
> 78 21 SOI
> media
> 78 21 Heading
> CM dies
> 78 21 Description
> In the agdadjkagdasgdjkhasdkajhgdhjsajhdgasdhgaksgda .....
>
> 80 22 SOI
> media
> 80 22 Units
> in the armed forces
> 80 22 EventLoc
> kashmir
> 80 22 GR
> encounter
> 80 22 Other Perspective ""
> 80 22 Heading
> A bomb takes 100 lives
>
> 78 23 Village
> chattarpur
> 78 23 SOI
> media
> 78 23 Heading
> PM address nation
> 78 23 Description
> on the eve of Republic day Pm addresses nation and ensures safety
> asjhdgakhgdjla....
>
> 80 22 SOI
> media
> 80 22 Units
> military academy
> 80 22 EventLoc
> Hyderabad
> 80 22 GR
> firing
> 80 22 Other Perspective ""
> 80 22 Heading
> militantas have a firing near military academy
>
> _*category_table :
>
> *_category_id category_name
> 78 Political
> 80 Criminal
> ..........
> .........
> ....
>
>
> Problem :-
>
> 1. There are more than 40000 rows and different *category_id* have
> different number of rows ( field_name,fild_values)
> 2. There may be case when different *category_id's* have different
> *field_name.
> 3. *In future there may be 1000 of categories and millions of news.
> *
> Requirement :
>
> * We want the desired data in horizontal format and field_name
> becomes the table columns for e.g ;
>
> A user inputs category = Criminal then output will be :
>
> *category_id category_name SOI
> Units EventLoc GR
> Other Perspective Heading*
> 80 Criminal media
> in the armed forces Kashmir encounter
> "" A bomb takes 100 lives
> 80 Criminal media
> military academy Hyderabad firing
> "" militantas have a firing near
> military academy
>
> ||rly catgory_id 78 has also its rows.
>
> Note that on other category_id column names may change.
>
> My procedure :
> 1. create a temporary table depending upon *field_name* column of
> the corresponding id.
> 2. Load data in it from user_news table.
> 3. Select data from the temporary table.
>
> I am not able to load data.
> Please guide what is the correct way to achieve this.
>
> I attach my procedure. Please let me know if any other information
> is required.
>
> I can do it myself if someone show me the path to do this
>
>
> Thanks & best Regards
> Adarsh Sharma
>
> 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);
>
>
>
> for name in select label_name from category_labels where
> category_id = (select category_id from category where
> category_name=cat_name) loop
>
> execute 'alter table temptest add column ' || name || ' text';
>
> end loop;
>
> select * from user_news where category_id=
>
> end;
>
> $$ language plpgsql;
>
>
> /* category_label table contains all the field_name entries of
> category_id's */
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> This looks like good candidate for "pivot" or "crosstab" functions.
> Had done something similar under Oracle, but in postgresql, there is
> tablefunc
> <http://www.postgresql.org/docs/current/static/tablefunc.html>in
> contrib. Hope that helps.
***************************************************************************************************************************************************
Thanks for your suggestions :

After following the below link

http://www.postgresql.org/docs/current/static/tablefunc.html

I have a doubt in mind, If i use the crosstab function as

SELECT *
FROM crosstab(
'select rowid, attribute, value
from ct
where attribute = ''att2'' or attribute = ''att3''
order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
test2 | val6 | val7 |

Output is generated but it is static, fore.g we have to mention all the output colummn names in the beginning but my column names also be different w.r.t different category_id's

ct(row_name text, category_1 text, category_2 text, category_3 text);

We have to give column names as shown above but it depends upon different category_id', they may be different.

Thanks
(2 rows)