I got solution using loop Logic...
DECLARE @PCWDtls TABLE (CLAUSEID INT IDENTITY(1,1),CLAUSEPCODE VARCHAR(40), CLAUSECODE VARCHAR(20), CLAUSEPLAN VARCHAR(20), CLAUSERATE VARCHAR(40),
CLAUSEPREM VARCHAR(40), CLAUSEINTNO VARCHAR(500), CLAUSEINTSEQ VARCHAR(500), CLAUSEINTSI VARCHAR(1000), CLAUSEINTPREM VARCHAR(1000))
DECLARE @PCWFinal TABLE(CLAUSECODE VARCHAR(20), CLAUSEINTNO VARCHAR(500), CLAUSEINTSEQ VARCHAR(500), CLAUSEINTSI VARCHAR(1000), CLAUSEINTPREM VARCHAR(1000), RECCOUNT INT)
INSERT INTO @PCWDtls(CLAUSEPCODE , CLAUSECODE , CLAUSEPLAN , CLAUSERATE , CLAUSEPREM , CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM)
SELECT 'FC819*FIRE','FC819','FIRE','25000000','-4350','1.2','1.2','20000000','17400'
UNION ALL
SELECT 'FC825(ii)*FIRE','FC825(ii)','FIRE','25000','2500','1.1','1.1','10000000','8700'
UNION ALL
SELECT 'FC825b(ii)*FIRE','FC825b(ii)','FIRE','2500','250','1.1','1.1','10000000','8700'
UNION ALL
SELECT 'FC842A*FIRE','FC842A','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'
UNION ALL
SELECT 'FC843*FIRE','FC843','FIRE','5000','1000','1.2','1.2','20000000','17400'
UNION ALL
SELECT 'FC845*FIRE','FC845','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'
UNION ALL
SELECT 'FC846.01*FIRE','FC846.01','FIRE','0','0','1.1ü1.2','1.1ü1.2','10000000ü20000000','8700ü17400'
SELECT CLAUSECODE , CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM
FROM @PCWDtls
DECLARE @RecCount INT, @I INT=1,
@CLAUSECODE VARCHAR(40), @CLAUSEINTNO VARCHAR(500), @CLAUSEINTSEQ VARCHAR(500), @CLAUSEINTSI VARCHAR(1000), @CLAUSEINTPREM VARCHAR(1000)
SELECT @RecCount = COUNT(CLAUSEID) FROM @PCWDtls
WHILE @I<=@RecCount
BEGIN
SELECT @CLAUSECODE=CLAUSECODE, @CLAUSEINTNO=CLAUSEINTNO, @CLAUSEINTSEQ=CLAUSEINTSEQ, @CLAUSEINTSI=CLAUSEINTSI, @CLAUSEINTPREM=CLAUSEINTPREM FROM @PCWDtls WHERE CLAUSEID=@I
IF CHARINDEX('ü',@CLAUSEINTNO,1)=0
BEGIN
INSERT INTO @PCWFinal(RECCOUNT, CLAUSECODE , CLAUSEINTNO , CLAUSEINTSEQ , CLAUSEINTSI , CLAUSEINTPREM)
SELECT 1, @CLAUSECODE , @CLAUSEINTNO , @CLAUSEINTSEQ , @CLAUSEINTSI , @CLAUSEINTPREM
END
ELSE
BEGIN
INSERT INTO @PCWFinal(RECCOUNT, CLAUSECODE , CLAUSEINTNO )
SELECT ROW_NUMBER()OVER(ORDER BY Item)'RECCOUNT',@CLAUSECODE,Item FROM dbo.fnSplitString(@CLAUSEINTNO,'ü')
UPDATE F SET F.CLAUSEINTSEQ=SEQ.Item, F.CLAUSEINTSI=SI.Item, F.CLAUSEINTPREM=PREM.Item
FROM @PCWFinal F
INNER JOIN (SELECT ROW_NUMBER()OVER(ORDER BY Item)'RECCOUNT',@CLAUSECODE 'CLAUSECODE',Item FROM dbo.fnSplitString(@CLAUSEINTSEQ,'ü')) SEQ ON SEQ.CLAUSECODE=F.CLAUSECODE AND SEQ.RECCOUNT=F.RECCOUNT
INNER JOIN (SELECT ROW_NUMBER()OVER(ORDER BY Item)'RECCOUNT',@CLAUSECODE 'CLAUSECODE',Item FROM dbo.fnSplitString(@CLAUSEINTSI,'ü')) SI ON SI.CLAUSECODE=F.CLAUSECODE AND SI.RECCOUNT=F.RECCOUNT
INNER JOIN (SELECT ROW_NUMBER()OVER(ORDER BY Item)'RECCOUNT',@CLAUSECODE 'CLAUSECODE',Item FROM dbo.fnSplitString(@CLAUSEINTPREM,'ü')) PREM ON PREM.CLAUSECODE=F.CLAUSECODE AND PREM.RECCOUNT=F.RECCOUNT
END
SELECT @I=@I+1
END
SELECT D.CLAUSEPCODE, D.CLAUSECODE, D.CLAUSEPLAN, D.CLAUSERATE, D.CLAUSEPREM, F.CLAUSEINTNO, F.CLAUSEINTSEQ, F.CLAUSEINTSI, F.CLAUSEINTPREM
FROM @PCWFinal F
INNER JOIN @PCWDtls D ON D.CLAUSECODE=F.CLAUSECODE
Other than this any solutions are there, Please let me know....
Regards,
GVPrabu