mysql动态sql 整理多个字段

原始表：

整理后的表：

方案一（动态sql）：

BEGIN

    #Routine body goes here...

    DECLARE v1 int();

    DECLARE v2 int();

    #DECLARE v3 VARCHAR();

    #DECLARE vcompany VARCHAR();

    #DECLARE vname VARCHAR();

    DECLARE vcol VARCHAR();

    DECLARE tmp_sql VARCHAR();

    DECLARE vid int();

    set v1 = ;

    WHILE v1 <=  DO

        set v2=;

        #if v2 <=27 THEN

        WHILE v2 <=  DO

            set @v3=CONCAT('l',v2);

            set @vcompany='';

            set @vname='';

            #set @vid='';

            SET @l_sql=CONCAT_ws(' ',

                'select company_name,',@v3,

                'into @vcompany,@vname',

                'from for_xun_cha where id =',v1);

            SET @sql=@l_sql;

            prepare stmt from @sql;

            execute stmt;

                #select company_name,l2 into vcompany,vname from for_xun_cha where id=v2;

            if LENGTH(trim(@vname)) >  THEN

                INSERT into tmp(company,name) VALUES(@vcompany,@vname);

                #INSERT into tmp(company,name) VALUES(vcompany,tmp_sql);

            end if;

            set v2 = v2+;

        #end if;

        end WHILE;

        set v1 = v1 + ;

    END WHILE;

END

方案二（先合并各列，再用游标处理）：

BEGIN

DECLARE Done INT DEFAULT ;

DECLARE n int ; -- 最大列

DECLARE companyName VARCHAR() ;-- 公司名称

DECLARE personNames VARCHAR();-- 员工名称

DECLARE personName VARCHAR();

DECLARE _cur CURSOR FOR select company_name ,CONCAT_WS(",",l2,l3,l4,l5,l6,l7,l8,l9,l10,l11,l12,l13,l14,l15,l16,l17,l18,l19,l20,l21,l22,l23,l24,l25,l26,l27) from for_xun_cha;

OPEN _cur;

    FETCH _cur INTO companyName,personNames;

    REPEAT

    IF NOT Done THEN

         test:    WHILE(n<=) DO

            set personName=SUBSTRING_INDEX(SUBSTRING_INDEX(personNames,',',n),',',-);

                if  ISNULL(personName) || LENGTH(trim(personName))< THEN

                        LEAVE test;

                ELSE

                    INSERT INTO temp1 VALUES (companyName,personName);

                END IF;

            set n=n+;

            end while;

    end IF;

set n = ;

    FETCH NEXT FROM _cur INTO companyName,personNames;

    UNTIL Done END REPEAT;

-- set personName="";

-- set personNames="";

CLOSE _cur;

END