之前sqlserver的行转列, 可以用pivot来写.

参考这篇文章 https://www.cnblogs.com/gaizai/p/3753296.html

根据自己项目实际表,改成这样

CREATE PROCEDURE [dbo].[usp_CRMBaseInfo] -- Add the parameters for the stored procedure here @table_code varchar(50)ASBEGIN DECLARE @sql_str VARCHAR(8000) DECLARE @sql_col VARCHAR(8000) SELECT @sql_col = ISNULL(@sql_col + ‘,‘,‘‘) + QUOTENAME([FieldCode]) FROM CRMBaseInfoDefineField where tablecode=@table_code SET @sql_str = ‘ SELECT * FROM (SELECT [RowGuid],[FieldValue],[FieldCode] FROM ( SELECT CRMBaseInfo.FieldValue, CRMBaseInfo.RowGuid, CRMBaseInfoDefineField.FieldCode FROM CRMBaseInfo RIGHT OUTER JOIN CRMBaseInfoDefineField ON CRMBaseInfo.Guid = CRMBaseInfoDefineField.Guid )as info) p PIVOT (Max([FieldValue]) FOR [FieldCode] IN ( ‘+ @sql_col +‘)) AS pvt ‘ PRINT (@sql_str) EXEC (@sql_str)END

但是在MySQL的存储过程要怎么写呢? 这就花了我一天时间,因为不熟悉MySQL  

参考这篇文章https://blog.csdn.net/isoleo/article/details/51726629

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_crmBaseInfo`(IN tablecode varchar(50))BEGINSET @sql = NULL;SELECT GROUP_CONCAT(DISTINCT CONCAT( ‘MAX(IF(c.FieldCode = ‘‘‘, c.FieldCode, ‘‘‘, s.FieldValue, ‘‘‘‘)) AS ‘‘‘, c.FieldCode, ‘\‘‘ ) ) INTO @sqlFROM CRMBaseInfoDefineField c where c.isActive=1 and c.tablecode=tablecode;select @sql;SET @sql = CONCAT(‘Select s.RowGuid, ‘, @sql, ‘ From CRMBaseInfoDefineField c Left Join CRMBaseInfo s On c.guid = s.guid ‘ ); SET @sql = CONCAT(@sql, ‘ Where c.tablecode = \‘‘, tablecode, ‘\‘‘);SET @sql = CONCAT(@sql, ‘ Group by s.RowGuid‘);select @sql;PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt; END