Adding process to SQL query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Adding process to SQL query

Adding process to SQL query

Rate Topic

Display Mode

Topic Options

Author

Message

sqlrd22

Posted Monday, September 03, 2012 7:26 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:35 AM
Points: 59, Visits: 682

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

t.brown 89142

Posted Tuesday, September 04, 2012 3:09 AM

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

Post #1353776

t.brown 89142

Posted Tuesday, September 04, 2012 4:32 AM

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

sqlrd22

Posted Tuesday, September 04, 2012 4:47 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:35 AM
Points: 59, Visits: 682

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

t.brown 89142

Posted Tuesday, September 04, 2012 8:34 AM

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

sqlrd22

Posted Tuesday, September 04, 2012 8:37 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:35 AM
Points: 59, Visits: 682

Thanks, I'll give it a go :)

Post #1353943

« Prev Topic | Next Topic »

Permissions