Insert data in one table from Another Problem

--------------000101070303010205080401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


Dear all,

Today I am puzzled around a problem of inserting data into new table in
new format. I have a table named *user_news* as :

We have four rows with respect to each record_id.

fore.g : I have listed main columns as
*record_id field_name field_value*
572 SOI Media
572 Heading A senior Police official confirmed the
presence of the stone quarry at Jafflong near the India-Bangladesh border
572 Description HNLC runs a stone quarry in Jafflong
District of Bangladesh. The outfit is also believed to own several betel
nut plantations besides running other business in Bangladesh.
572 RelLoc Jafflong

578 SOI Media
578 Heading Army Chief General V. K. Singh in Shillong
said he was confident that the NDFB would come to the negotiating table
if they are "handled properly"
578 Description A school teacher was abducted by
unidentified militants in Damas of East Garo Hills District. Army Chief
General V. K. Singh in Shillong said he was confident .
578 RelLoc Garo Hills

Similarly i have 1000 of rows.

Now I create a new table as columns as :

*record_id SOI heading Description RelLoc * and its
values is as :
*

*
572 Media A senior Police official confirmed the
presence of the stone quarry at Jafflong near the India-Bangladesh
border HNLC runs a stone quarry in Jafflong District of
Bangladesh. Jafflong






The values in *field_name* becomes four columns in the above table . and
their values are the values of f*ield_value *column.

The problem is that I want this data now in horizontal form and the data
of four rows in one row. That is four rows in one table contributes a
single row in *other *table.

I try with procedures and cursors but fail to achieve the output.

Is it possible in Mysql. Please guide me how to achieve this as I am
stuck around it.



Thanks & Best Regards

Adarsh Sharma






------------------------------------------------------------ ------------





--------------000101070303010205080401--

RE: Insert data in one table from Another Problem

Here's one option to "pivot" your results:

select
record_id
,max(soi) as soi
,max(heading) as heading
,max(description) as description
,max(relloc) as relloc
from
(select
record_id
,if(field_name = 'SOI', field_value, '') as soi
,if(field_name = 'Heading', field_value, '') as heading
,if(field_name = 'Description', field_value, '') as description
,if(field_name = 'RelLoc', field_value, '') as relloc
from user_news) s1
group by s1.record_id;



-----Original Message-----
From: Adarsh Sharma [mailto:adarsh.sharma@orkash.com]
Sent: Wednesday, February 16, 2011 6:33 AM
To: mysql@lists.mysql.com
Subject: Insert data in one table from Another Problem


Dear all,

Today I am puzzled around a problem of inserting data into new table in
new format. I have a table named *user_news* as :

We have four rows with respect to each record_id.

fore.g : I have listed main columns as
*record_id field_name field_value*
572 SOI Media
572 Heading A senior Police official confirmed the
presence of the stone quarry at Jafflong near the India-Bangladesh border
572 Description HNLC runs a stone quarry in Jafflong
District of Bangladesh. The outfit is also believed to own several betel
nut plantations besides running other business in Bangladesh.
572 RelLoc Jafflong

578 SOI Media
578 Heading Army Chief General V. K. Singh in Shillong
said he was confident that the NDFB would come to the negotiating table
if they are "handled properly"
578 Description A school teacher was abducted by
unidentified militants in Damas of East Garo Hills District. Army Chief
General V. K. Singh in Shillong said he was confident .
578 RelLoc Garo Hills

Similarly i have 1000 of rows.

Now I create a new table as columns as :

*record_id SOI heading Description RelLoc * and its
values is as :
*

*
572 Media A senior Police official confirmed the
presence of the stone quarry at Jafflong near the India-Bangladesh
border HNLC runs a stone quarry in Jafflong District of
Bangladesh. Jafflong






The values in *field_name* becomes four columns in the above table . and
their values are the values of f*ield_value *column.

The problem is that I want this data now in horizontal form and the data
of four rows in one row. That is four rows in one table contributes a
single row in *other *table.

I try with procedures and cursors but fail to achieve the output.

Is it possible in Mysql. Please guide me how to achieve this as I am
stuck around it.



Thanks & Best Regards

Adarsh Sharma






------------------------------------------------------------ ------------






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org