CLIENT SUPPORT » Asuransi Bintang - Existing

USE [Bintang_Prod]

GO

/****** Object:  StoredProcedure [dbo].[spUtilSplitToTable7Column]    Script Date: 1/18/2022 9:19:56 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spUtilSplitToTable7Column]

	@sInputList VARCHAR(MAX)

	, @sDelimiterRow VARCHAR(MAX)

	, @sDelimiterColumn VARCHAR(MAX)

AS

--declare @sInputList VARCHAR(MAX)='|S4. Leadership (Planning & Monitoring, Decision Making, People Development),25,Level 03,,-3,0,0||T13. Bintang Product Knowledge,5,Level 03,,-3,0,0'

--declare @sDelimiterRow VARCHAR(MAX)='|'

--declare @sDelimiterColumn VARCHAR(MAX)=','

	--Variable Declaration

	DECLARE @row_temp TABLE (item varchar(MAX))

	DECLARE @col_temp TABLE (

		col1 varchar(MAX),

		col2 varchar(MAX),

		col3 varchar(MAX),

		col4 varchar(MAX),

		col5 varchar(MAX),

		col6 varchar(MAX),

		col7 varchar(MAX)

	)

	DECLARE @row_count int, @col_pos int

	DECLARE @col1 varchar(MAX),

		@col2 varchar(MAX),

		@col3 varchar(MAX),

		@col4 varchar(MAX),

		@col5 varchar(MAX),

		@col6 varchar(MAX),

		@col7 varchar(MAX)

	--Processing Row

	INSERT INTO @row_temp

	SELECT item FROM dbo.fnUtilSplit(@sInputList, @sDelimiterRow)

	SELECT @row_count = COUNT(*) FROM @row_temp

	--Processing Column

	IF (@row_count > 0)

	BEGIN

		DECLARE cur CURSOR

		FOR SELECT item FROM @row_temp 

		DECLARE @cur_status int, @col_txt varchar(MAX), @col_txt_reverse varchar(MAX)

		SET @cur_status = 0

		OPEN cur

		WHILE @cur_status = 0

		BEGIN

			SET @col_txt = ''

			SET @col_txt_reverse = ''

			FETCH NEXT FROM cur INTO @col_txt

			SET @cur_status = @@FETCH_STATUS

			IF @cur_status = 0

			BEGIN

				SET @col_txt_reverse = REVERSE(@col_txt)

				SET @col_pos = CHARINDEX(@sDelimiterColumn, @col_txt_reverse, 0)

				SET @col6=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,1,@col_pos-1)))

				SET @col6 =  REVERSE(@col6)

				SET @col_txt_reverse=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,@col_pos+LEN(@sDelimiterColumn),LEN(@col_txt_reverse))))

				SET @col_pos = CHARINDEX(@sDelimiterColumn, @col_txt_reverse, 0)

				SET @col5=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,1,@col_pos-1)))

				SET @col5 =  REVERSE(@col5)

				SET @col_txt_reverse=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,@col_pos+LEN(@sDelimiterColumn),LEN(@col_txt_reverse))))

				SET @col_pos = CHARINDEX(@sDelimiterColumn, @col_txt_reverse, 0)

				SET @col4=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,1,@col_pos-1)))

				SET @col4 =  REVERSE(@col4)

				SET @col_txt_reverse=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,@col_pos+LEN(@sDelimiterColumn),LEN(@col_txt_reverse))))

				SET @col_pos = CHARINDEX(@sDelimiterColumn, @col_txt_reverse, 0)

				SET @col3=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,1,@col_pos-1)))

				SET @col3 =  REVERSE(@col3)

				SET @col_txt_reverse=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,@col_pos+LEN(@sDelimiterColumn),LEN(@col_txt_reverse))))

				SET @col_pos = CHARINDEX(@sDelimiterColumn, @col_txt_reverse, 0)

				SET @col2=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,1,@col_pos-1)))

				SET @col2 =  REVERSE(@col2)

				SET @col_txt_reverse=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,@col_pos+LEN(@sDelimiterColumn),LEN(@col_txt_reverse))))

				SET @col_pos = CHARINDEX(@sDelimiterColumn, @col_txt_reverse, 0)

				SET @col1=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,1,@col_pos-1)))

				SET @col1 =  REVERSE(@col1)

				SET @col_txt_reverse=RTRIM(LTRIM(SUBSTRING(@col_txt_reverse,@col_pos+LEN(@sDelimiterColumn),LEN(@col_txt_reverse))))

				INSERT INTO @col_temp VALUES( REVERSE(@col_txt_reverse), @col6, @col5, @col4, @col3, @col2, @col1)

			END

		END

		CLOSE cur

		DEALLOCATE cur

	END

	SELECT * FROM @col_temp