Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding process to SQL query Expand / Collapse
Author
Message
Posted Monday, September 3, 2012 7:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, 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.
Post #1353518
Posted Tuesday, September 4, 2012 3:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, 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.
Post #1353776
Posted Tuesday, September 4, 2012 4:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, 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%'

Post #1353796
Posted Tuesday, September 4, 2012 4:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, 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.
Post #1353802
Posted Tuesday, September 4, 2012 8:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 27, 2012 9:02 AM
Points: 114, 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;




Post #1353939
Posted Tuesday, September 4, 2012 8:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 5:13 AM
Points: 61, Visits: 721
Thanks, I'll give it a go :)
Post #1353943
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse