Comma seperated field to arrays for each record? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Comma seperated field to arrays for each record?

Author

Topic

nmiddleweek
Starting Member

3 Posts

Posted - 2008-05-12 : 09:36:03

Hello,

My SQL knowledge is limited so if I get stuff wrong then correct me... but I can imagine this task will be quite testing...

I am working on a system that logs ([Audit] table) the changes to fields on some tables using a Trigger on UPDATE. I need to produce a 'quick' report that returns the date when the tables overallStatus field was set to 1.

In the [Audit] table I can find all the field changes for the record in question using this SQL...

select *
from audit
where rowid = 1309606
order by auditID asc

My problem is filtering this data. The fields I need are formatted as below, see records returned, in ASC order...

audit.AuditID = 2652583
audit.OperationTime = 2008-04-24 15:12:07.740
audit.ColumnDetail = 'estimatedProductionPriceactualProductionCost'
audit.EditDetail = '0.00000.0000'

audit.AuditID = 2658460
audit.OperationTime = 2008-04-25 10:51:47.930
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '05'

audit.AuditID = 2665723
audit.OperationTime = 2008-04-25 22:06:50.200
audit.ColumnDetail = 'overallStatusdespatchDateInsertionStatus'
audit.EditDetail = '1 3'

audit.AuditID = 2711092
audit.OperationTime = 2008-04-30 17:22:12.593
audit.ColumnDetail = 'overallStatusInsertionStatus'
audit.EditDetail = '34'

audit.AuditID = 2713217
audit.OperationTime = 2008-04-30 20:46:34.817
audit.ColumnDetail = 'clientOrderNumber'
audit.EditDetail = 'PAT12P7640'

The funny character, , is ASCII 127.

So need to find when 1309606's overallStatus was changed to 1. Manually looking at the data, I can see overallStatus was modified in 3 of the above 5 [Audit] records. It started life as 0, then went to 1 and then to 3. I'm aware that I need to look at the previous Audits date for when it was changed to the value I'm looking for... So it was changed to 1 on 2008-04-25 10:51:47.930.

What is the best way to approach this problem? I'm hoping to use T-SQL only and not have to use an external scripting language, unless I can embed vbscript or jscript inside a T-SQL function and then use arrays, etc?

Somehow I need to convert the ASCII(127) seperated list of fieldname into an array or list, find the index of the fieldname 'overallStatus' and then lookup that value in the datafield.

Hope that makes sense and any help would be great!

Cheers,
Nick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 10:18:23

Make a UDF like this:-

CREATE FUNCTION GetValues
(
@ColString varchar(max),
@ValString varchar(max),
@Delimiter char(1)
)
RETURNS @Results TABLE
(
ID int IDENTITY(1,1),
Column varchar(100),
Value varchar(100)
)

AS

DECLARE @Col varchar(100),@Val varchar(100)


WHILE @ColString IS NOT NULL AND @ValString IS NOT NULL
BEGIN
SELECT @Col=CASE
          WHEN CHARINDEX(CHAR(127),@ColString) >0 THEN LEFT(@ColString,CHARINDEX(CHAR(127),@ColString)-1)
          ELSE @ColString
          END,
       @Val=CASE
          WHEN CHARINDEX(CHAR(127),@ValString) >0 THEN LEFT(@ColString,CHARINDEX(CHAR(127),@ValString)-1)
          ELSE @ValString
          END

INSERT INTO @Results (Column,Value)
SELECT @Col,@Val

SELECT @ColString=CASE
          WHEN CHARINDEX(CHAR(127),@ColString) >0 THEN SUBSTRING(@ColString,CHARINDEX(CHAR(127),@ColString)+1,LEN(@ColString))
          ELSE NULL
          END,
       @ValString=CASE
          WHEN CHARINDEX(CHAR(127),@ValString) >0 THEN SUBSTRING(@ValString,CHARINDEX(CHAR(127),@ValString)+1,LEN(@ValString))
          ELSE NULL
          END

END
GO

And try using it like this

DECLARE @RowID=1309606

;
With CTE (RowNo,AuditID,OpeartionTime,Value) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY AuditID),
a.AuditID,a.OperationTime,b.Value
FROM Audit a
CROSS APPLY (SELECT Value
             FROM GetValues(a.ColumnDetail,a.EditDetail,CHAR(127)
             WHERE Column='overallStatus') b
WHERE RowId=@RowID
)

SELECT c1.OpeartionTime
FROM CTE c1
INNER JOIN CTE c2
ON c2.RowNo=c1.RowNo + 1
WHERE c2.Value=1

nmiddleweek
Starting Member

3 Posts

Posted - 2008-05-12 : 12:09:31

visakh16,

Thanks for you reply on this - not that I understand it all but I get the idea and it that looks like what I need. So I convert the strings to a 2 col temp table and then use that for the lookups.

I've copied the code into SQL Management Studio and parsed it. I got an error at Column and Value varchar(100) cause of reserved words. I'm now getting an error on this line..

DECLARE @Col varchar(100), @Val varchar(100)

Msg 156, Level 15, State 1, Procedure fnc_Split, Line 17
Incorrect syntax near the keyword 'DECLARE'.

I've tried looking at other functions that return temp tables but I can't find any that define the fields like you have, they just return columns from select statements and not dynamically INSERT the data to the returned table.

I'm sure it's something obvious?

Thanks,
Nick

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 12:31:54

quote:
Originally posted by nmiddleweek

visakh16,

Thanks for you reply on this - not that I understand it all but I get the idea and it that looks like what I need. So I convert the strings to a 2 col temp table and then use that for the lookups.

I've copied the code into SQL Management Studio and parsed it. I got an error at Column and Value varchar(100) cause of reserved words. I'm now getting an error on this line..

DECLARE @Col varchar(100), @Val varchar(100)

Msg 156, Level 15, State 1, Procedure fnc_Split, Line 17
Incorrect syntax near the keyword 'DECLARE'.

I've tried looking at other functions that return temp tables but I can't find any that define the fields like you have, they just return columns from select statements and not dynamically INSERT the data to the returned table.

I'm sure it's something obvious?

Thanks,
Nick

i missed a return at the end

CREATE FUNCTION GetValues
(
@ColString varchar(max),
@ValString varchar(max),
@Delimiter char(1)
)
RETURNS @Results TABLE
(
ID int IDENTITY(1,1),
Column varchar(100),
Value varchar(100)
)

AS
BEGIN
DECLARE @Col varchar(100),@Val varchar(100)


WHILE @ColString IS NOT NULL AND @ValString IS NOT NULL
BEGIN
SELECT @Col=CASE
          WHEN CHARINDEX(CHAR(127),@ColString) >0 THEN LEFT(@ColString,CHARINDEX(CHAR(127),@ColString)-1)
          ELSE @ColString
          END,
       @Val=CASE
          WHEN CHARINDEX(CHAR(127),@ValString) >0 THEN LEFT(@ColString,CHARINDEX(CHAR(127),@ValString)-1)
          ELSE @ValString
          END

INSERT INTO @Results (Column,Value)
SELECT @Col,@Val

SELECT @ColString=CASE
          WHEN CHARINDEX(CHAR(127),@ColString) >0 THEN SUBSTRING(@ColString,CHARINDEX(CHAR(127),@ColString)+1,LEN(@ColString))
          ELSE NULL
          END,
       @ValString=CASE
          WHEN CHARINDEX(CHAR(127),@ValString) >0 THEN SUBSTRING(@ValString,CHARINDEX(CHAR(127),@ValString)+1,LEN(@ValString))
          ELSE NULL
          END

END

RETURN
END
GO

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-12 : 12:59:46

I've been having fun with this

.

I've no idea if it will be useful, and the performance is probably horrendous, but this is what I came up with...

-- Structure and Data
declare @audit table (AuditID int, OperationTime datetime, ColumnDetail varchar(100), EditDetail varchar(100))
insert @audit
          select 2652583, '2008-04-24 15:12:07.740', 'estimatedProductionPrice' + char(127) + 'actualProductionCost' + char(127), '0.0000' + char(127) + '0.0000' + char(127)
union all select 2658460, '2008-04-25 10:51:47.930', 'overallStatus' + char(127) + 'InsertionStatus' + char(127), '0' + char(127) + '5' + char(127)
union all select 2665723, '2008-04-25 22:06:50.200', 'overallStatus' + char(127) + 'despatchDate' + char(127) + 'InsertionStatus' + char(127), '1' + char(127) + char(127) + '3' + char(127)
union all select 2711092, '2008-04-30 17:22:12.593', 'overallStatus' + char(127) + 'InsertionStatus' + char(127), '3' + char(127) + '4' + char(127)
union all select 2713217, '2008-04-30 20:46:34.817', 'clientOrderNumber' + char(127), 'PAT12P7640' + char(127)

-- Inputs
declare @Col varchar(30); set @Col = 'overallStatus';
declare @Val varchar(30); set @Val = '1';

-- Calculation
; with
Numbers as (SELECT distinct Number as i FROM Master.dbo.spt_values where number between 1 and 20),
a as (select AuditID, OperationTime,
        replace(char(127) + ColumnDetail, char(127), '" /><col name="') cols,
        replace(char(127) + EditDetail, char(127), '" /><val name="') vals
        from @audit),
b as (select AuditID, OperationTime,
        cast(substring(cols, 5, len(cols)-15) as xml) colXml,
        cast(substring(vals, 5, len(vals)-15) as xml) valXml
        from a),
c as (select AuditID, OperationTime,
        colXml.query('(/col[position() = sql:column("c.i")])[1]').value('(//@name)[1]', 'varchar(50)') Col,
        valXml.query('(/val[position() = sql:column("c.i")])[1]').value('(//@name)[1]', 'varchar(50)') Val
        from b cross join Numbers c),
d as (select * from c where Col = @Col)
select a.*, b.OperationTime as PreviousOperationTime, b.val as PreviousVal
from d a outer apply (select top 1 * from d where Col = a.Col and OperationTime < a.OperationTime order by OperationTime desc) b
where a.Val = @Val

/* Results
AuditID     OperationTime           Col                 Val        PreviousOperationTime   PreviousVal
----------- ----------------------- ------------------- ---------- ----------------------- -------------
2665723     2008-04-25 22:06:50.200 overallStatus       1          2008-04-25 10:51:47.930 0
*/

Edit: a few minor tweaks.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

nmiddleweek
Starting Member

3 Posts

Posted - 2008-05-12 : 13:00:28

Hello,

Thanks again... that's working ok now...

I'm now using the second part to call the GetValues function... I had to split the top line into seperate DECLARE and SET statements but I'm now getting an Incorrect Syntax near the keyword WHERE after the CROSS APPLY...

I've changed Column to fldColumn to match my change in the function created so it's not that. See below for code I'm using...

DECLARE @RowID int
SET @RowID = 1309606

;
With CTE (RowNo,AuditID,OpeartionTime,Value) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY AuditID), a.AuditID, a.OperationTime, b.fldValue
FROM Audit a
CROSS APPLY (SELECT fldValue
FROM GetValues(a.ColumnDetail,a.EditDetail,CHAR(127)
WHERE fldColumn = 'overallStatus') b --ERRORS HERE
WHERE RowId = @RowID
)

SELECT c1.OperationTime
FROM CTE c1
INNER JOIN CTE c2
ON c2.RowNo=c1.RowNo + 1
WHERE c2.Value=1

Cheers,
Nick

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-12 : 13:04:31

FROM GetValues(a.ColumnDetail,a.EditDetail,CHAR(127)) <-- need extra bracket?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources