CODEWITHSUNDEEP
mysqlsqlcsvpivotaggregate-functions
Baljot Singh
Baljot Singh

Reputation: 143

How to convert row data into columns in mysql?

I need help to convert the data in a following format from 2 columns (ID & description ) to 6 further columns shown below . Will appreciate the suggestions provided.

       Id  Description                                                                               
    ------  ------------------------------------------------------------------------------------------
         1  Company : RAFIQ BRAZING WORK                                                              
         1  Factory Address : Plot No. 2, 87/B/12, Shop No. 1 , Nr. Lalit                             
         1  Factory Address : Engineering, G.I.D.C., Umber, Dist, Valsad - 7482
 ,               
         1  Factory Address :  ITHDH                                                                
         1  Contact Name : Mr. Adam Noor / Mr. Noor,                                                                                                       
         1  Mobile : 8888761 9323                                       
         1  Product : MS Steel,                                
         1  Product : Copper                                        
         2  Company : ComapSAPNA STEEL                                                                
         2  Factory Address: Plot No. 1909, Ill Phase, GIDC, Umbergoan,                               
         2  Factory Address : Dist. Valsad 5er 5334, Arat                                        
         2  Mobile : 0260-32517320 Fax: 0260-2562133                                                  
         2  Contact Name: Mr. Farukh Abdulla Mobile: 6667027032                                      
         2  Email: farbdulla@ gmail.com                                                           
         2  Office address: Gala no. 3, B.T. Compound, malad west, Mumbai - 407777                                                                           
         2  Contact Name: Mr. Hamsa Abdulla Mobile:093333732768                                                                                                 
         2  Product: Specialist in Profile Cutting, Traders of M.S.Plate,                             
         2  Product : Angels Channels, etc. 



ID   Company           contactperson                   mobilenumber       products      factoryaddress
1  RAFIQ BRAZING WORK  Mr. Adam Noor/ Mr. Noor    +8888761 9323      MS Steel, Copper   Plot No. X, 19/B/12, Shop No. 1 , Nr. Lalit Engineering, G.I.D.C., Umber, Dist, Valsad - 7482 ,ITHDH

Upvotes: 0

Views: 305

Answers (2)

MUKUL HOSEN
MUKUL HOSEN

Reputation: 104

Unlike some other RDBMS MySQL doesn't have native support for pivoting operations of this sort by design (the developers feel it's more suited to the presentation, rather than database, layer of your application).

If you absolutely must perfom such manipulations within MySQL, building a prepared statement is the way to go—although rather than messing around with CASE, I'd probably just use MySQL's GROUP_CONCAT() function:

SELECT CONCAT(
  'SELECT `table`.id', GROUP_CONCAT('
     ,    `t_', REPLACE(name, '`', '``'), '`.value
         AS `', REPLACE(name, '`', '``'), '`'
     SEPARATOR ''),
 ' FROM `table` ', GROUP_CONCAT('
     LEFT JOIN `table`   AS `t_', REPLACE(name, '`', '``'), '`
            ON `table`.id = `t_', REPLACE(name, '`', '``'), '`.id
           AND `t_', REPLACE(name, '`', '``'), '`.name = ', QUOTE(name)
     SEPARATOR ''),
 ' GROUP BY `table`.id'
) INTO @qry FROM (SELECT DISTINCT name FROM `table`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;

Note that the result of GROUP_CONCAT() is limited by the group_concat_max_len variable (default of 1024 bytes: unlikely to be relevant here unless you have some extremely long name values).

Upvotes: 0

GMB
GMB

Reputation: 222642

That’s a poor data model. Each attribute should be stored as a column rather than buried in a string.

For your setup, assuming that ' : ' consistently separates the attribute name from its value, you could use string functions and conditional aggregation like this:

select id,
    group_concat(case when attr = 'Company' then val end) as company,
    group_concat(case when attr = 'Factory Address' then val end) as factory_address,
    ...
from (
    select t.*,
        left(description, locate(' : ', description) - 1) as attr,
        substring(description, locate(' : ', description) + 3) as val 
    from mytable t
) t
group by id

The subquery parses the string as an attribute/value pair, then the outer query aggregates.

Upvotes: 1

Related Questions