在mysql中如何将逗号分隔的列转换成多行？

CREATE  PROCEDURE `proc_GetTreeViewData_test`(IN WONO INT(11))
 BEGIN
    DROP TEMPORARY TABLE IF EXISTS tblWOTypeMapping;
     CREATE TEMPORARY TABLE tblWOTypeMapping (ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, CurrentWOTypeId int(11), ParrentWOTypeId int(11));
     INSERT INTO tblWOTypeMapping(CurrentWOTypeId, ParrentWOTypeId)VALUES(4,1);
     INSERT INTO tblWOTypeMapping(CurrentWOTypeId, ParrentWOTypeId)VALUES(1,2);
     INSERT INTO tblWOTypeMapping(CurrentWOTypeId, ParrentWOTypeId)VALUES(2,3);
    DROP TEMPORARY TABLE IF EXISTS tblTreeViewDetail;
     CREATE TEMPORARY TABLE tblTreeViewDetail (P3_WOTypeId int(11), P3_WOType varchar(20), WOID int(11), WONumber varchar(20), WOStatus int(11), ParentWONumber int(11), isLink int(10));
     INSERT INTO tblTreeViewDetail(P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus,ParentWONumber,isLink)
     SELECT p3_womaster.P3_WOTypeId, p3_wotype.P3_WOType, p3_womaster.P3_WOId, p3_womaster.P3_WONo, p3_womaster.P3_WOStatusId, p3_womaster.P3_WOParentWONo, 1 FROM p3_womaster
     JOIN p3_wotype on (p3_womaster.P3_WOTypeId = p3_wotype.P3_WOTypeId) WHERE P3_WONo = WONO;
     SET @P3_WOTypeId = (select P3_WOTypeId from p3_womaster where P3_WONO = WONO);
     SET @WONOTemp = WONO;
     While @P3_WOTypeId <> 3 DO
         SET @P3_WOParentWONO = (SELECT IFNull(P3_WOParentWONO,'') from p3_womaster where P3_WONO = @WONOTemp);
         IF (@P3_WOParentWONO  IS NULL OR @P3_WOParentWONO = '') then
                SET @MappingCounter = (SELECT ID from tblWOTypeMapping where CurrentWOTypeId = @P3_WOTypeId);
                 SET @MAXMappingCounter = (SELECT count(*) from tblWOTypeMapping);
                WHILE @MappingCounter < @MAXMappingCounter DO
                    INSERT INTO tblTreeViewDetail(P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus,ParentWONumber,isLink)
                     SELECT tblWOTypeMapping.ParrentWOTypeId, p3_wotype.P3_WOType, 0, 0, 0, 0,0 
                    from tblWOTypeMapping JOIN p3_wotype on (tblWOTypeMapping.ParrentWOTypeId = p3_wotype.P3_WOTypeId)
                    WHERE tblWOTypeMapping.ID = @MappingCounter;
                    SET @MappingCounter = @MappingCounter +1;
                 END WHILE;
                SET @P3_WOTypeId = 3;
            ELSE
                SET @WONOTemp = @P3_WOParentWONO;
                 SET @ExistFlag  = (select count(P3_WOID) from p3_womaster where P3_WONO = @WONOTemp);
                 IF(@ExistFlag  is not null AND @ExistFlag  > 0) then 
                    INSERT INTO tblTreeViewDetail(P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus,ParentWONumber,isLink)
                    SELECT p3_womaster.P3_WOTypeId, p3_wotype.P3_WOType, p3_womaster.P3_WOID, p3_womaster.P3_WONo, p3_womaster.P3_WOStatusId, p3_womaster.P3_WOParentWONo, 1 
                    from p3_womaster JOIN p3_wotype on (p3_womaster.P3_WOTypeId = p3_wotype.P3_WOTypeId)
                    WHERE p3_womaster.P3_WONO = @WONOTemp;
                 ELSE
                    SET  @P3_WOTypeIdTemp = (SELECT ParrentWOTypeId from tblWOTypeMapping where CurrentWOTypeId = @P3_WOTypeId);
                    INSERT INTO tblTreeViewDetail(P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus,ParentWONumber,isLink)
                    SELECT @P3_WOTypeIdTemp, p3_wotype.P3_WOType, 0, @WONOTemp, 0, 0, 0 
                    from p3_wotype WHERE p3_wotype.P3_WOTypeId = @P3_WOTypeIdTemp;
                 END IF;
                 SET @P3_WOTypeId = (SELECT P3_WOTypeId from p3_womaster where P3_WONO = @WONOTemp);
             END IF;
     END WHILE;
    DROP TEMPORARY TABLE IF EXISTS TempTabletoStoreChildWO;
    CREATE TEMPORARY TABLE TempTabletoStoreChildWO (ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, P3_WOTypeId int(11), P3_WOId int(11), P3_WONo varchar(20), P3_StatusId int(11));
     SET @WONOTofindChild = WONO;
    INSERT into TempTabletoStoreChildWO (P3_WOTypeId, P3_WOId, P3_WONo, P3_StatusId)
    SELECT P3_WOTypeId, P3_WOId, P3_WONo, P3_WOStatusId from p3_womaster where P3_WOParentWONO = @WONOTofindChild;
     SET @TableMAXrow = (select count(*) from TempTabletoStoreChildWO);
     SET @TableCounter = 1; 
     WHILE @TableCounter <= @TableMAXrow DO
         INSERT INTO tblTreeViewDetail(P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus,ParentWONumber,isLink)
         SELECT TempTabletoStoreChildWO.P3_WOTypeId, p3_wotype.P3_WOType, TempTabletoStoreChildWO.P3_WOId, TempTabletoStoreChildWO.P3_WONo, TempTabletoStoreChildWO.P3_StatusId, @WONOTofindChild,1 
         from TempTabletoStoreChildWO JOIN p3_wotype on (TempTabletoStoreChildWO.P3_WOTypeId = p3_wotype.P3_WOTypeId)  Where ID = @TableCounter;
         INSERT INTO tblTreeViewDetail(P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus,ParentWONumber,isLink)
         SELECT p3_womaster.P3_WOTypeId, p3_wotype.P3_WOType, p3_womaster.P3_WOId, p3_womaster.P3_WONo, p3_womaster.P3_WOStatusId, p3_womaster.P3_WOParentWONO,1 
         FROM p3_womaster JOIN p3_wotype on (p3_womaster.P3_WOTypeId = p3_wotype.P3_WOTypeId) WHERE P3_WOParentWONO IN (
         SELECT P3_WONo from TempTabletoStoreChildWO Where ID = @TableCounter);
         INSERT INTO tblTreeViewDetail(P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus,ParentWONumber,isLink)
        SELECT p3_womaster.P3_WOTypeId, p3_wotype.P3_WOType, p3_womaster.P3_WOId, p3_womaster.P3_WONo, p3_womaster.P3_WOStatusId, p3_womaster.P3_WOParentWONO,1 
         FROM p3_womaster JOIN p3_wotype on (p3_womaster.P3_WOTypeId = p3_wotype.P3_WOTypeId) WHERE P3_WOParentWONO IN (
        SELECT P3_WONo FROM p3_womaster where P3_WOParentWONO IN (SELECT P3_WONo from TempTabletoStoreChildWO Where ID = @TableCounter));
         INSERT INTO tblTreeViewDetail(P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus,ParentWONumber,isLink)
         SELECT p3_womaster.P3_WOTypeId, p3_wotype.P3_WOType, p3_womaster.P3_WOId, p3_womaster.P3_WONo, p3_womaster.P3_WOStatusId, p3_womaster.P3_WOParentWONO,1 
         FROM p3_womaster JOIN p3_wotype on (p3_womaster.P3_WOTypeId = p3_wotype.P3_WOTypeId) WHERE P3_WOParentWONO IN (
        SELECT P3_WONo FROM p3_womaster where P3_WOParentWONO IN (SELECT P3_WONo FROM p3_womaster where P3_WOParentWONO IN (
         SELECT P3_WONo from TempTabletoStoreChildWO Where ID = @TableCounter)));
        SET @TableCounter = @TableCounter +1;
     END WHILE;
     SELECT tblTreeViewDetail.P3_WOTypeId, tblTreeViewDetail.P3_WOType, tblTreeViewDetail.WOID, tblTreeViewDetail.WONumber, tblTreeViewDetail.WOStatus,
     p3_wostatusmaster.P3_WOStatusDescription,
     tblTreeViewDetail.ParentWONumber,tblTreeViewDetail.isLink, 
     (SELECT P3_CompletionPercentage from p3_processmaster where P3_ProcessId in 
     (select P3_ProcessId from p3_woprocess where P3_WOProcessId in (SELECT MAX(P3_WOProcessId) from p3_woprocess WHERE P3_WOID = tblTreeViewDetail.WOID group by P3_WOID))) as CompletionPercentage,
    p3_womaster.P3_WOPlannedDeliveryDate
     from tblTreeViewDetail LEFT JOIN p3_womaster on (tblTreeViewDetail.WONumber = p3_womaster.P3_WONO) 
     LEFT JOIN p3_wostatusmaster on (tblTreeViewDetail.WOStatus = p3_wostatusmaster.P3_WOStatusId) 
     Where tblTreeViewDetail.P3_WOTypeId  <> 3;
     DROP TEMPORARY TABLE IF EXISTS TempTabletoStoreChildWO;
     DROP TEMPORARY TABLE IF EXISTS tblTreeViewDetail;
     DROP TEMPORARY TABLE IF EXISTS tblWOTypeMapping;
 END

输出如下：


# P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus, P3_WOStatusDescription, ParentWONumber, isLink, CompletionPercentage, P3_WOPlannedDeliveryDate
'2', 'PWO', '5484', '121298982', '24', 'Yet To Start', '66656,121298981', '1', NULL, NULL
'1', 'AWO', '5485', '121298983', '24', 'Yet To Start', '6754,121298982', '1', '0%', NULL
'4', 'IO', '5486', '121298984', '24', 'Yet To Start', '121298983', '1', '0%', NULL

需要以下输出：


# P3_WOTypeId, P3_WOType, WOID, WONumber, WOStatus, P3_WOStatusDescription, ParentWONumber, isLink, CompletionPercentage, P3_WOPlannedDeliveryDate
'2', 'PWO', '5484', '121298982', '24', 'Yet To Start', '121298981', '1', NULL, NULL
'2', 'PWO', '5484', '121298982', '24', 'Yet To Start', '66656', '1', NULL, NULL
'1', 'AWO', '5485', '121298983', '24', 'Yet To Start', '121298982', '1', '0%', NULL
'4', 'IO', '5486', '121298984', '24', 'Yet To Start', '121298983', '1', '0%', NULL
'4', 'IO', '5486', '121298984', '24', 'Yet To Start', '6754', '1', '0%', NULL

在mysql中如何将逗号分隔的列转换成多行？

暂无答案！

相关问题

热门标签

最新问答