October 29, 2009 at 9:18 am
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
October 29, 2009 at 9:40 am
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
October 30, 2009 at 1:32 am
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
October 30, 2009 at 8:26 am
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...
October 30, 2009 at 9:16 am
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
November 2, 2009 at 3:56 am
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.
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
November 2, 2009 at 7:00 am
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
November 2, 2009 at 8:56 am
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! 😎
November 2, 2009 at 9:26 am
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
May 3, 2014 at 7:50 pm
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