Re: How to do this ?

Any update on this.

Please guide.

Adarsh Sharma 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,field_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 explain the procedure below. 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 */

********************************************************