mysql 动态行转列

之前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)

AS
BEGIN
    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))
BEGIN

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(c.FieldCode = ''',
      c.FieldCode,
      ''', s.FieldValue, '''')) AS ''',
      c.FieldCode, '''
    )
  ) INTO @sql
FROM 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