List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:February 17 2011 8:35pm
Subject:RE: Insert data in one table from Another Problem
View as plain text  
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@stripped] 
Sent: Wednesday, February 16, 2011 6:33 AM
To: mysql@stripped
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






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





Thread
Insert data in one table from Another ProblemAdarsh Sharma16 Feb
  • RE: Insert data in one table from Another ProblemTravis Ard17 Feb