如何在mysql中将行数据转换为列

Question

我需要帮助将以下格式的数据从2列(ID&description(转换为下面显示的6列。将感谢您提供的建议。

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 : [phone]                                       
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 : [phone] Fax: [phone]                                                  
2  Contact Name: Mr. Farukh Abdulla Mobile: [phone]                                      
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:[phone]                                                                                                 
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    [phone]      MS Steel, Copper   Plot No. X, 19/B/12, Shop No. 1 , Nr. Lalit Engineering, G.I.D.C., Umber, Dist, Valsad - 7482 ,ITHDH

Answer 1

这是一个糟糕的数据模型。每个属性都应存储为列，而不是隐藏在字符串中。

对于您的设置，假设' : '一致地将属性名称与其值分开，您可以使用字符串函数和条件聚合，如下所示：

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

子查询将字符串解析为属性/值对，然后外部查询聚合。

Answer 2

与其他一些RDBMS不同，MySQL在设计上没有对此类中枢操作的本地支持(开发人员认为它更适合应用程序的表示层，而不是数据库层(。

如果你绝对必须在MySQL中执行这样的操作，那么构建一个准备好的语句是最好的方法——尽管我可能只使用MySQL的GROUP_CONCT((函数：

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;

请注意，GROUP_CONNCT((的结果受到GROUP_CONCAT_max_len变量的限制(默认值为1024字节：除非您有一些超长的名称值，否则在这里不太可能相关(。