Re: How to do this ?

[Adarsh Sharma <adarsh.sharma@xxxxxxxxxx>]

Esmin Gracic wrote:

On Wed, May 18, 2011 at 1:25 PM, Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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 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)