InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 02:51:25 GMT20http://www.sqlservercentral.com/Forums/Topic1353518-1292-1.aspxThanks, I'll give it a go :)Tue, 04 Sep 2012 08:37:07 GMTsqlrd22http://www.sqlservercentral.com/Forums/Topic1353518-1292-1.aspxOK this is not ideal, as its proving to be quite tricky, however its *nearly* thereThere are 2 problems1) it only goes up to column 48 - that is because of the self-join I've done in PartResults2 on RowNum + 1and 2) The final result has 48 Rows, not 1 row, There is a separate row for each [EAx] [x] pair, so somethings up with the Pivot. I think its to do with the MAX(NEWDATA) Perhaps this will give you something to build on ... [code="sql"];WITH PartResult1 AS ( SELECT INDEX1, (SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.INDEX1 = so1.INDEX1 and so2.ROWNUM = 8) AS [Date] , (SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.INDEX1 = so1.INDEX1 and so2.ROWNUM = 14) AS [MPAN] , ROW_NUMBER()OVER(PARTITION BY INDEX1 ORDER BY ROWNUM) AS ROWNUM, (SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = so1.ROWNUM +3) AS BITOFDATA , -- THIS LINE Gets the Data from the E/A = item before above (SELECT BITOFDATA FROM #SPLITOUTDATA so5 WHERE so5.index1 = so1.index1 and so5.rownum = so1.rownum +2) AS [E/A] FROM #SPLITOUTDATA so1 WHERE BITOFDATA = '012' ), PartResults2 AS ( -- this bit partitions the results to get 1,2 for EA, and BitOfData SELECT PR.INDEX1, PR.Date, PR.MPAN, PR.ROWNUM, 'EA' + CONVERT(varchar,PR.RowNum) AS EANum, PR.BITOFDATA, PR.[E/A], ROW_NUMBER() OVER( Partition BY PR.RowNum Order BY PR.RowNum) AS Partn FROM PartResult1 AS PR JOIN PartResult1 AS PR1 ON PR1.ROWNUM >= PR.ROWNUM AND PR1.ROWNUM <= PR.ROWNUM + 1), PartResults3 AS (SELECT PR2.INDEX1, PR2.DATE, PR2.MPAN, PR2.ROWNUM, CASE PR2.PArtn WHEN 1 THEN EANum WHEN 2 THEN CONVERT(varchar,ROWNUM) END AS NEWROWNUM, CASE PR2.PArtn WHEN 1 THEN PR2.[E/A] WHEN 2 then CONVERT(varchar,PR2.BITOFDATA) END AS NEWDATA, PR2.BITOFDATAFROM PartResults2 AS PR2)SELECT [Date], [MPAN],[EA1], [1], [EA2], [2], [EA3], [3], [EA4], [4], [EA5], [5], [EA6], [6], [EA7], [7], [EA8], [8], [EA9], [9], [EA10], [10], [EA11], [11], [EA12], [12], [EA13], [13], [EA14], [14], [EA15], [15], [EA16], [16], [EA17], [17], [EA18], [18], [EA19], [19], [EA20], [20], [EA21], [21], [EA22], [22], [EA23], [23], [EA24], [24], [EA25], [25], [EA26], [26], [EA27], [27], [EA28], [28], [EA29], [29], [EA30], [30], [EA31], [31], [EA32], [32], [EA33], [33], [EA34], [34], [EA35], [35], [EA36], [36], [EA37], [37], [EA38], [38], [EA39], [39], [EA40], [40], [EA41], [41], [EA42], [42], [EA43], [43], [EA44], [44], [EA45], [45], [EA46], [46], [EA47], [47], [EA48]FROM PartResults3PIVOT (MAX (NEWDATA) FOR NEWROWNUM IN ( [EA1], [1], [EA2], [2], [EA3], [3], [EA4], [4], [EA5], [5], [EA6], [6], [EA7], [7], [EA8], [8], [EA9], [9], [EA10], [10], [EA11], [11], [EA12], [12], [EA13], [13], [EA14], [14], [EA15], [15], [EA16], [16], [EA17], [17], [EA18], [18], [EA19], [19], [EA20], [20], [EA21], [21], [EA22], [22], [EA23], [23], [EA24], [24], [EA25], [25], [EA26], [26], [EA27], [27], [EA28], [28], [EA29], [29], [EA30], [30], [EA31], [31], [EA32], [32], [EA33], [33], [EA34], [34], [EA35], [35], [EA36], [36], [EA37], [37], [EA38], [38], [EA39], [39], [EA40], [40], [EA41], [41], [EA42], [42], [EA43], [43], [EA44], [44], [EA45], [45], [EA46], [46], [EA47], [47], [EA48]) )AS PVT;[/code]Tue, 04 Sep 2012 08:34:39 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1353518-1292-1.aspxI have sent you the real data in a private message as I do not wish to post it on here. It should work from that, thanks.Tue, 04 Sep 2012 04:47:02 GMTsqlrd22http://www.sqlservercentral.com/Forums/Topic1353518-1292-1.aspxHiThanks for the updates. I've managed to get your code working.My result is gets the same column output as yours, but no dataI Just added your datavalue to the RUG_CLOB Table DATA column, with arbitrary index1 of 1I think its failing to pick up the DATA value because of this line[code="sql"]CAST(DATA AS VARCHAR(MAX)) LIKE 'ZHV|FS0000%%%|D0003001%'[/code]Tue, 04 Sep 2012 04:32:25 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1353518-1292-1.aspxHi your post has not been ignored, its just very complex for a 'newbie' question. I can't actually answer your question yet, though I have had a look.You have a complex splitter function using XML followed by some pivoting - hardly newbie material. Some advice: (to get better answers) 1) Change to subject to metion the XML splitter function and Pivot - to attract people who know about those subjects2) Make it easier for people to test your query by providing CREATE TABLE statements and some INSERTS for sample data - it may be a pain doing it, but if I could cut/paste direct into management studio and run the code, I would be much nearer to working out an answer.3) If your data is sensitive - you may need to simplify the tables, and change the function accordingly.And welcome to the forum: Only joined myself a few weeks ago and its been an invaluable resource. :-DTue, 04 Sep 2012 03:09:25 GMTt.brown 89142http://www.sqlservercentral.com/Forums/Topic1353518-1292-1.aspxI use the code below to extract a piece of data that is so many along in the delimited string after a certain character or number appears, I wish to pull another piece of data to appear before each of these values being pulled. I do not fully understand how the code works, although I do understand elements of it. I just wish to repeat the process of pulling on another bit of data (one piece before the value currently being pulled) and integrate it into the query results.[code]USE RUG_Data IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;CREATE TABLE #SPLITOUTDATA ( INDEX1 INT, ROWNUM INT, BITOFDATA VARCHAR(max));IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XMLGOSET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONGOCREATE 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;WITHREFORMATTEDDATA AS(SELECT ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS INDEX1, REPLACE(REPLACE(CAST(DATA AS VARCHAR(MAX)),CHAR(13),''),CHAR(10),'')AS RAWCLOB2FROM RUG_CLOBWHERE CAST(DATA AS VARCHAR(MAX)) LIKE 'ZHV|FS0000%%%|D0003001%')INSERT INTO #SPLITOUTDATA SELECT INDEX1, ROW_NUMBER()OVER(PARTITION BY INDEX1 ORDER BY split.ItemNumber) AS ROWNUM, split.ItemValue AS BITOFDATA FROM REFORMATTEDDATA CROSS APPLY dbo.Split_XML(reformatteddata.RAWCLOB2,'|') SPLITCREATE CLUSTERED INDEX idx1 ON #SPLITOUTDATA (INDEX1,ROWNUM)SELECT [Date], [MPAN], [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],[49],[50] FROM( SELECT INDEX1, (SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.INDEX1 = so1.INDEX1 and so2.ROWNUM = 8) AS [Date] , (SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.INDEX1 = so1.INDEX1 and so2.ROWNUM = 14) AS [MPAN] , ROW_NUMBER()OVER(PARTITION BY INDEX1 ORDER BY ROWNUM) AS ROWNUM, (SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = so1.ROWNUM +3) AS BITOFDATA FROM #SPLITOUTDATA so1 WHERE BITOFDATA = '012' -- 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],[49],[50]) ) AS PVTIF 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[/code]At the moment my query results show as –[code][Date], [MPAN], [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],[49],[50] [/code]I wish for it to be -[code][Date], [MPAN], [E/A],[1], [E/A], [2], [E/A], [3], [E/A], [4], [E/A], [5], [E/A], [6], [E/A], [7], [E/A], [8], [E/A], [9], [E/A], [10], [E/A], [11], [E/A], [12], [E/A], [13], [E/A], [14], [E/A], [15], [E/A], [16], [E/A], [17], [E/A], [18], [E/A], [19], [E/A],[20], [E/A], [21], [E/A], [22], [E/A], [23], [E/A], [24], [E/A], [25], [E/A], [26], [E/A], [27], [E/A],[28], [E/A], [29], [E/A], [30], [E/A], [31], [E/A], [32], [E/A], [33], [E/A], [34], [E/A],[35], [E/A],[36], [E/A], [37], [E/A], [38], [E/A], [39], [E/A], [40], [E/A], [41], [E/A], [42], [E/A], [43], [E/A], [44], [E/A], [45], [E/A], [46], [E/A], [47], [E/A], [48], [E/A],[49], [E/A], [50] [/code]The E/A value is also within the delimited string and features one piece before the value I am pulling every time so I assume in some way I will use this bit of code to pull it:[code] (SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = so1.ROWNUM +2) AS BITOFDATA FROM #SPLITOUTDATA so1 WHERE BITOFDATA = '012' [/code]I just can’t get it to work for some reason as I’m fairly new at this but I assume it’s fairly simple. Sorry if I haven't explained it very well.Update: Create table statement:[code]USE [RUG_Data]GO/****** Object: Table [dbo].[RUG_CLOB] Script Date: 04/09/2012 10:18:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[RUG_CLOB]( [INDEX1] [int] NULL, [DATA] [varchar](max) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGO[/code]Sample data:20120831120000|S|010||011|20120831|012|01|A|7|||||012|02|A|7|||||012|03|A|7|||||012|04|A|7|||||012|05|A|7|||||012|06|A|7|||||012|07|A|7|||||012|08|A|7|||||012|09|A|7|||||012|10|A|7|||||012|11|A|7|||||012|12|A|7|||||012|13|A|7|||||012|14|A|7|||||012|15|A|7|||||012|16|A|7|||||012|17|A|7|||||012|18|A|7|||||012|19|A|7|||||012|20|A|7|||||012|21|A|7|||||012|22|A|7|||||012|23|A|7|||||012|24|A|7|||||012|25|A|7|||||012|26|A|7|||||012|27|A|7|||||012|28|A|7|||||012|29|A|7|||||012|30|A|7|||||012|31|A|7|||||012|32|A|7|||||012|33|A|7|||||012|34|A|7|||||012|35|A|7|||||012|36|A|7|||||012|37|A|7|||||012|38|A|7|||||012|39|A|7|||||012|40|A|7|||||012|41|A|7|||||012|42|A|7|||||012|43|A|7|||||012|44|A|7|||||012|45|A|7|||||012|46|A|7|||||012|47|A|7|||||012|48|A|7||This is data that is dumped into the 'Data' column, the Index1 column is not of much use.Mon, 03 Sep 2012 07:26:34 GMTsqlrd22