USE GATEKEEPER 

IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;
CREATE TABLE #SPLITOUTDATA (
	DC_INDEX_FK INT,
	ROWNUM INT,
	BITOFDATA VARCHAR(max));


IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XML
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.Split_XML
    (
     @Parameter VARCHAR(MAX)
    ,@Delimiter VARCHAR(1)
    )
RETURNS @Result TABLE
    (
     ItemNumber INT
    ,ItemValue VARCHAR(MAX)
    )
AS 
    BEGIN
        DECLARE @XML XML ;
        SET @Parameter = ( SELECT   @Parameter
                         FOR XML PATH('')
                         ) ;
        SELECT  @XML = '<r>' + REPLACE(@Parameter, @Delimiter, '</r><r>') + '</r>' ;

        INSERT  INTO @Result
                (
                 ItemNumber
                ,ItemValue
                )
                SELECT  ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL) ) AS ItemNumber
                ,       Item.value('text()[1]', 'VARCHAR(MAX)') AS ItemValue
                FROM    @XML.nodes('//r') R ( Item ) ;
        RETURN ;
    END ;
GO
;WITH
REFORMATTEDDATA AS
(
SELECT  
	DC_INDEX_FK,
	REPLACE(REPLACE(CAST(RAW_CLOB AS VARCHAR(MAX)),CHAR(13),''),CHAR(10),'')AS RAWCLOB2
FROM
	DATAFLOW_CLOB
WHERE
	CAST(RAW_CLOB AS VARCHAR(MAX)) LIKE 'AAA|S0141007|%201208__|%'
)


INSERT INTO #SPLITOUTDATA 
SELECT 
      DC_INDEX_FK, 
      ROW_NUMBER()OVER(PARTITION BY DC_INDEX_FK ORDER BY split.ItemNumber) AS ROWNUM,
      split.ItemValue AS BITOFDATA 
 FROM REFORMATTEDDATA
 CROSS APPLY dbo.Split_XML(reformatteddata.RAWCLOB2,'|') SPLIT


CREATE CLUSTERED INDEX idx1 ON #SPLITOUTDATA (DC_INDEX_FK,ROWNUM)


SELECT
	 
	[Date],
	RunType,
	[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],
	[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48] 

FROM
( 
	SELECT 
	DC_INDEX_FK, 
	(SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.DC_INDEX_FK = so1.DC_INDEX_FK and so2.ROWNUM = 12) AS [Date] ,
	(SELECT BITOFDATA FROM #SPLITOUTDATA so3 WHERE so3.DC_INDEX_FK = so1.DC_INDEX_FK and so3.ROWNUM = 13) AS RunType ,
	ROW_NUMBER()OVER(PARTITION BY DC_INDEX_FK ORDER BY ROWNUM) AS ROWNUM, 




	(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.DC_INDEX_FK = so1.DC_INDEX_FK AND so4.ROWNUM = so1.ROWNUM +14) AS BITOFDATA 
	FROM #SPLITOUTDATA so1 
	WHERE BITOFDATA = 'SPH' 

	AND 
		(SELECT BITOFDATA FROM #SPLITOUTDATA so5 WHERE so5.DC_INDEX_FK = so1.DC_INDEX_FK AND so5.ROWNUM = 10) NOT IN ('TR01')
	) p 
	PIVOT 
		(MAX (BITOFDATA) 
	FOR ROWNUM IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48]) 
) AS PVT



IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;
IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XML