Before SQL Server row to column, you can use pivot to write.
Refer to this article https://www.cnblogs.com/gaizai/p/3753296.html
According to the actual table of your project, change to this
CREATE PROCEDURE [dbo]. [Usp_crmbaseinfo]--Add The parameters forThe stored procedure here @table_code varchar ( -) Asbegin DECLARE @sql_str VARCHAR (8000) DECLARE @sql_col VARCHAR (8000) SELECT @sql_col= ISNULL (@sql_col +',',"') + QUOTENAME ([Fieldcode]) from CrmbaseinfodefinefieldwhereTablecode=@table_code SET @sql_str='SELECT *From (select [Rowguid],[fieldvalue],[fieldcode] FROM (select Crmbaseinfo.fieldvalue, Crmbaseinf O.rowguid, Crmbaseinfodefinefield.fieldcode from Crmbaseinfo right OUTER JOIN Crmbaseinfodefinefield on Crmbas Einfo.guid=crmbaseinfodefinefield.guid) asinfo) P PIVOT (Max ([Fieldvalue]) for [Fieldcode] in ('+ @sql_col +')) AS Pvt'PRINT (@sql_str) EXEC (@sql_str) END
But how do you write the stored procedure in MySQL? It took me a day, because I was not familiar with MySQL
Refer to this article 51726629
CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Usp_crmbaseinfo ' (inchTablecodevarchar( -))BEGINSET @sql = NULL;SELECTGroup_concat (DISTINCTCONCAT ('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 ('SelectS.rowguid,', @sql,' fromCrmbaseinfodefinefield C Left JoinCrmbaseinfo s onC.guid=S.guid' ); SET @sql = CONCAT (@sql,' WhereC.tablecode=\"', Tablecode,'\"'); SET @sql = CONCAT (@sql,' Group byS.rowguid'); select @sql; PREPARE stmt from @sql; EXECUTE stmt;deallocate PREPARE stmt; END
MySQL Dynamic row to column