Key Values pairs in string to table

  • Hi there,

    I kind of got stuck with the following problem:

    A logger (it's Nlog to be specific) logs information from our application into the database. Since it only has a limited number of layouts we use the message field to write a hell of a lot of key value pairs into the database like 'a=1;b=2;c=3;'.

    I now wrote a stored procedure which first of all splits this string into its key value pairs into a temptable so row per row i then have a=1 and b=2 and so on with a corresponding id in the first column.

    I then use T-SQl-Commands LEFT / RIGHT to update the fields key and value which are part of the tempptable to get the key (left part of ',') and the value (right part of ','). Then I get all disctint keys for being able to pivot the table with all columns.

    CREATE PROCEDURE SP_LogView

    -- Add the parameters for the stored procedure here

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Declare temptable

    CREATE TABLE #FirstSplit

    (

    uid [int],

    string nvarchar(200),

    nvarchar(100) NULL,

    [value] nvarchar(100) NULL,

    [timestamp] DateTime

    )

    --declare variables

    declare @message nvarchar(255)

    declare @element nvarchar(255)

    declare @uid int

    declare @timestamp DateTime

    DECLARE @query VARCHAR(8000)

    DECLARE @columns VARCHAR(8000)

    -- get values for cursor from logtable

    declare MessageList cursor for

    SELECT [message], uid, longdate FROM dbo.logtable WHERE MESSAGE LIKE 'STEP%'

    OPEN MessageList

    FETCH NEXT FROM MessageList

    INTO @message, @uid, @timestamp

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- do the first split by ;

    INSERT INTO #FirstSplit ([uid], string, [timestamp]) SELECT @uid, Element, @timestamp FROM dbo.[TF_Split](@message, ';')

    FETCH NEXT FROM MessageList INTO @message, @uid, @timestamp

    END

    CLOSE MessageList

    DEALLOCATE MessageList

    -- Do second split by = to get key and value and update into table

    UPDATE #FirstSplit SET = LEFT(string,CHARINDEX('=',string)-1)

    UPDATE #FirstSplit SET [value] = RIGHT(string,LEN(string)-CHARINDEX('=',string))

    -- drop column string

    ALTER TABLE #FirstSplit DROP COLUMN string

    -- dynamically get all column names for pivoting table

    SELECT @columns = COALESCE(@columns + ',[' + cast([Key] as varchar) + ']',

    '[' + cast([Key] as varchar)+ ']')

    FROM #FirstSplit

    GROUP BY [Key]

    -- pivot the temptable

    SET @query = '

    SELECT *

    FROM #FirstSplit

    PIVOT

    (

    MAX([Value])

    FOR [Key]

    IN (' + @columns + ')

    )

    AS p'

    -- execute pivot

    EXECUTE(@query)

    END

    Problem now is that I would like to be able to set view on top of this which is of course not really possible since i don't kow the numbers of columns and their names in the beginning. Any suggestions to work around that thing?

    Cheers and thanks in advance

    Daniel

  • There are two things. 1) Split the values in to separate columns 2) define view.

    For point no 1) You can use Tally tables (Search for "Tally table" in SSC) which avoids loops.

    For point 2) Can you describe the problem little indepth with example, then you get faster responses.

    Regards

    Jus

  • Thanks Jus for the Tally hint.

    Still I don't really know on how to accomplish this task. I have set up a script where you can see where I want to get. You need a dbo.Tally in the current database to execute the script.

    USE [Colt]

    GO

    /****** Object: StoredProcedure [dbo].[SP_LogView2] Script Date: 10/30/2009 08:13:25 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author:<Author,,Name>

    -- Create date: <Create Date,,>

    -- Description:<Description,,>

    -- =============================================

    ALTER PROCEDURE [dbo].[SP_LogView2]

    -- Add the parameters for the stored procedure here

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Declare temptable

    CREATE TABLE #FirstSplit

    (

    uid int NOT NULL,

    string nvarchar(200) NOT NULL,

    nvarchar(100) NULL,

    [value] nvarchar(100) NULL

    )

    CREATE TABLE #LOGTABLE

    (

    uid int IDENTITY(1,1) NOT NULL,

    MESSAGE NVARCHAR(200),

    CONSTRAINT PK_#LOGTABLE_N PRIMARY KEY CLUSTERED (uid)

    )

    INSERT INTO #LOGTABLE (MESSAGE) VALUES ('Step=ProcessDB;DatabaseID=40;CubeID=19;PlanID=2226;Region=159;MeasureGroupName=PlanDataTypeClass;rc=0')

    INSERT INTO #LOGTABLE (MESSAGE) VALUES ('Step=ProcessDB;DatabaseID=20;CubeID=10;PlanID=2226;Region=789;MeasureGroupName=PlanDataTypeClass;rc=0')

    INSERT INTO #LOGTABLE (MESSAGE) VALUES ('Step=ProcessDB;DatabaseID=30;CubeID=15;PlanID=2226;Region=123;MeasureGroupName=PlanDataTypeClass;rc=0')

    INSERT INTO #LOGTABLE (MESSAGE) VALUES ('Step=ProcessDB;DatabaseID=100;CubeID=99;PlanID=2226;Region=9989;MeasureGroupName=PlanDataTypeClass;rc=0')

    SELECT * FROM #LOGTABLE

    --declare variables

    declare @message nvarchar(255)

    declare @element nvarchar(255)

    declare @uid int

    declare @timestamp DateTime

    DECLARE @query VARCHAR(8000)

    DECLARE @columns VARCHAR(8000)

    --Do split via Tally

    INSERT INTO #FirstSplit (uid, string)

    SELECT mh.uid, SUBSTRING(';'+mh.MESSAGE+';',N+1,CHARINDEX(';',';'+mh.MESSAGE+';',N+1)-N-1)

    AS String

    FROM dbo.Tally t

    CROSS JOIN #LOGTABLE mh

    WHERE N < LEN(';'+mh.MESSAGE+';')

    AND SUBSTRING(';'+mh.MESSAGE+';',N,1) = ';'

    -- Do second split by = to get key and value and update into table

    UPDATE #FirstSplit SET = LEFT(string,CHARINDEX('=',string)-1)

    UPDATE #FirstSplit SET [value] = RIGHT(string,LEN(string)-CHARINDEX('=',string))

    -- drop column string

    ALTER TABLE #FirstSplit DROP COLUMN string

    SELECT * FROM #FirstSplit

    -- dynamically get all column names for pivoting table

    SELECT @columns = COALESCE(@columns + ',[' + cast([Key] as varchar) + ']',

    '[' + cast([Key] as varchar)+ ']')

    FROM #FirstSplit

    GROUP BY [Key]

    -- pivot the temptable

    SET @query = '

    SELECT *

    FROM #FirstSplit

    PIVOT

    (

    MAX([Value])

    FOR [Key]

    IN (' + @columns + ')

    )

    AS p'

    -- execute pivot

    EXECUTE(@query)

    DROP TABLE #LOGTABLE

    DROP TABLE #FirstSplit

    END

    As you can see I need a tempTable to work on which makes it imposssible to use a TVF for an on-the-fly display.

    How would you solve that problem? I probably need to define all the columns somewhere, isn't it?

    Thanks!

    Daniel

  • Do you get the fixed number of columns as output everytime you execute [SP_LogView2]

    --- If Yes, You can have a permanent table with those many column (you can truncate eachtime you execute)

    -- If No, you can change the final query like

    SET @query = '

    SELECT * into FinalCols

    FROM #FirstSplit

    PIVOT

    (

    MAX([Value])

    FOR [Key]

    IN (' + @columns + ')

    )

    AS p

    Now, you can drop this table (FinalCols) everytime from calling proc or in the [SP_LogView2]

    itself.

    Since this is permanent table, you can get all the column names dynamically from using syscolumns table...

  • Two questions:

    Why a view?

    Are the names in the name-value pairs from a fixed set of possible values, or truly dynamic? Are the names user-generated at runtime, or do they come out of a table of possible characteristics?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hi there,

    sorry for my late answer but weekend was just what I needed 😉

    @Jus: The hint with the table is something I already thought of. But let's get to the next question to see why it's not really an option.

    @GSquared:

    Why a view?

    The purpose of the transformation is to have a look at a real-time logging of our application. In my eyes it would be the best if the logger itself would just write the entries into seperate columns but it can't - it just delivers this string. In order to have a real-time look at the log entries I do not want to fire an SP every time to be able to have a look at it. I would prefer a view or a TVF. But i cannnot call an SP inside a view and I cannot really modify data inside a TVF.

    The numbers of columns could be delimited by me so I could at least have some kind of static code.

    Any other suggestions on how to make this transformation on the fly?

    Rgds.

    Daniel

  • Do you have a Numbers table? (Or Tally table, or whatever else you want to call it.)

    If so, then you can use a string parsing query in a cross apply to split up the pairs, then another to split up the names and values, then pivot that. Do the whole split functionality in a CTE, and the pivot in the final, outer select, and you could have a view on it. You just have to predefine the valid values for the name part of the pairs, and build those into your pivot statement.

    Are you familiar with Cross Apply, and with Numbers-based string parsing?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks GSquared! Got the solution now, just needed an inspration 😉 CTE is the solution. Will try to use them more often in future.

    Code now is:

    ALTER View [dbo].[LogView_V]

    AS

    /*

    2009-11-02DanielCreated (string looks like Step=xxx;Pllanid=1234;cubeid=9999)

    */

    --Build CTE to reuse in the following query

    WITH FirstLogView([uid], String, , [value], [instimestamp])

    AS

    (

    SELECT mh.uid, SUBSTRING(';' + mh.MESSAGE ,N+1,CHARINDEX(';',';'+mh.MESSAGE+';',N+1)-N-1)

    AS String, NULL as , NULL as [value], mh.longdate as [instimestamp]

    FROM dbo.Tally t

    CROSS JOIN dbo.logtable mh

    WHERE N < LEN(';' + mh.MESSAGE)

    AND SUBSTRING(';' + mh.MESSAGE ,N,1) = ';'

    AND mh.Message LIKE '***%'

    AND UID NOT IN (SELECT [UID] FROM dbo.LogView)

    )

    SELECT * FROM

    (

    --Call CTE and do a split via the char '=' to fill and [value] columns

    SELECT

    [uid],

    REPLACE(LEFT(string,CHARINDEX('=',string)-1), '***', '') as ,

    RIGHT(string,LEN(string)-CHARINDEX('=',string)) as [value],

    [instimestamp]

    FROM FirstLogView

    ) as a

    --PIVOT the result via static fields like [Step],[DatabaseID],[CubeID],[PlanID],[RegionId],[MeasureGroupName],[rc]

    PIVOT

    (

    MAX(a.[Value])

    FOR a.[Key]

    IN ([Step],[DatabaseID],[CubeID],[PlanID],[Region],[MeasureGroupName],[rc])

    ) as PivotedCTE

    As you see I use tally table (@Jus: Thanks for that interesting hint) and was able to define the columns statically.

    Done! Thanks to you guys! 😎

  • You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hi,

    This is a nice post, but I am trying to solve a similar problem.

    Can anyone please clarify the following doubts for me ?

    a. What does Logview stands for (It's used in the with clause where UID is not in )

    b. What's the UID in logtable. Is it a primary key on that table ?

    Thanks a lot in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

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