Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding process to SQL query


Adding process to SQL query

Author
Message
sqlrd22
sqlrd22
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 721
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.
t.brown 89142
t.brown 89142
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 140
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. :-D
t.brown 89142
t.brown 89142
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 140
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
sqlrd22
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 721
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
t.brown 89142
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 140
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
sqlrd22
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 721
Thanks, I'll give it a go Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search