Thread: How to do this ?
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
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
Esmin Gracic wrote:
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
***************************************************************************************************************************************************On Wed, May 18, 2011 at 1:25 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote: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.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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)
Any update on this.
Please guide.
Adarsh Sharma wrote:
****************************
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 */
********************************************************
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 */
********************************************************