How to seperate a data on comma and insert into new row

Posted by Shivu under Others on 12/14/2011 | Points: 10 | Views : 1158 | Status : [Member] | Replies : 1
I have a table as attached image.
I need values to be separated by comma and inserted into new rows.

http://stackoverflow.com/questions/5096584/how-to-expand-comma-separated-field-into-multiple-rows-in-mysql

I have used the cursor given in the above link, But there is wrong in my MySql Syntax.

I'm not getting the correct looping.

DROP PROCEDURE IF EXISTS `kavan_db`.`sp_splitReceipts`;

DELIMITER |

CREATE PROCEDURE `kavan_db`.`sp_splitReceipts` ()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE _ID INT(11);
DECLARE _BILL_NAME VARCHAR(255);
DECLARE _BILL_AMOUNT VARCHAR(255);
DECLARE _IX INT(11);
declare XC cursor for SELECT PaymentTypeID,BILL_NAME,BILL_AMOUNT FROM vw_receipts;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN XC;
read_loop: LOOP
FETCH XC INTO _ID,_BILL_NAME,_BILL_AMOUNT;
IF done THEN
LEAVE read_loop;
END IF;
SET _IX=1;
WHILE (LOCATE(',',_BILL_NAME)>0) DO
INSERT INTO tbl_receiptspost
(PaymentTypeId,Bill,Amount)
VALUES
( _ID,LTRIM(RTRIM(SUBSTRING(_BILL_NAME,1,LOCATE(',',_BILL_NAME)-1))),LTRIM(RTRIM(SUBSTRING(_BILL_AMOUNT,1,LOCATE(',',_BILL_AMOUNT)-1))));
set _ix = _ix + 1;
SET _BILL_NAME=SUBSTRING(_BILL_NAME,LOCATE(',',_BILL_NAME)+1,CHAR_LENGTH(_BILL_NAME));
SET _BILL_AMOUNT=SUBSTRING(_BILL_AMOUNT,LOCATE(',',_BILL_AMOUNT)+1,CHAR_LENGTH(_BILL_AMOUNT));
FETCH XC INTO _ID,_BILL_NAME,_BILL_AMOUNT;
END WHILE;
END LOOP;
CLOSE XC;
END




Responses

Posted by: Niladri.Biswas on: 6/4/2013 [Member] Platinum | Points: 25

Up
0
Down
try http://www.mssqltips.com/sqlservertip/1771/splitting-delimited-strings-using-xml-in-sql-server/

Best Regards,
Niladri Biswas

Shivu, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response