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