Adding process to SQL query

Question

Adding process to SQL query

sqlrd22

SSCrazy

Points: 2255
More actions
September 3, 2012 at 7:26 am

#393076

I 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.
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_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
ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS INDEX1,
REPLACE(REPLACE(CAST(DATA AS VARCHAR(MAX)),CHAR(13),''),CHAR(10),'')AS RAWCLOB2
FROM
RUG_CLOB
WHERE
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,'|') SPLIT
CREATE 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 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
At the moment my query results show as –
[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]
I wish for it to be -
[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]
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:
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = so1.ROWNUM +2) AS BITOFDATA
FROM #SPLITOUTDATA so1
WHERE BITOFDATA = '012'
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:
USE [RUG_Data]
GO
/****** Object: Table [dbo].[RUG_CLOB] Script Date: 04/09/2012 10:18:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RUG_CLOB](
[INDEX1] [int] NULL,
[DATA] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 1

Hi 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 subjects

2) 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. 😀

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 2

Hi

Thanks for the updates. I've managed to get your code working.

My result is gets the same column output as yours, but no data

I Just added your datavalue to the RUG_CLOB Table DATA column, with arbitrary index1 of 1

I think its failing to pick up the DATA value because of this line

CAST(DATA AS VARCHAR(MAX)) LIKE 'ZHV|FS0000%%%|D0003001%'

sqlrd22 SSCrazy Points: 2255 More actions · Answer 3

I 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.

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 4

OK this is not ideal, as its proving to be quite tricky, however its *nearly* there

There are 2 problems

1) it only goes up to column 48 - that is because of the self-join I've done in PartResults2 on RowNum + 1

and

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 ...

;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.BITOFDATA

FROM 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 PartResults3

PIVOT

(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;

sqlrd22 SSCrazy Points: 2255 More actions · Answer 5

sqlrd22

SSCrazy

Points: 2255

September 4, 2012 at 8:37 am

#1533315

Thanks, I'll give it a go 🙂