I have used script table as, hope this is okay.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE mytable(

[workflowId] [uniqueidentifier] NOT NULL,

[created] [datetime] NOT NULL,

[logType] [smallint] NOT NULL,

[activityName] [nvarchar](50) NULL,

[agentType] [int] NULL,

[agentSource] [sql_variant] NULL,

[agentKey] [sql_variant] NULL,

[agentName] [nvarchar](70) NULL,

[outcome] [nvarchar](50) NULL,

[details] [nvarchar](max) NULL

)

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table

INSERT INTO #mytable

(workflowId,created,logType,activityName,agentType,agentSource,agentName,outcome,details)

SELECT 'B316','Mar 15 2013 12:45PM','310','SalesDirector',2,AgentServic,'Steve','NULL','Sales Director - Please Approve Proposal' UNION ALL

SELECT 'B316','Mar 15 2013 12:45PM','316','Technical',2,AgentServic,'John','Checked','Performed By System Administrator Account' UNION ALL

SELECT 'B316','Mar 15 2013 12:45PM','310','Technical',2,AgentServic,'John','NULL','Pre-Sales - Check Technical Details' UNION ALL

SELECT 'B316','Mar 15 2013 12:45PM','316','TechReject',2,AgentServic,'Fred','Re Submit','Performed By System Administrator Account' UNION ALL

SELECT 'B316','Mar 15 2013 12:45PM','310','TechReject',2,AgentServic,'Fred','NULL','Originator - Technical Rejection' UNION ALL

SELECT 'B316','Mar 15 2013 12:45PM','316','Technical',2,AgentServic,'John','Reject','Performed By System Administrator Account' UNION ALL

SELECT 'B316','Mar 14 2013 11:46AM','310','Technical',2,AgentServic,'John','NULL','Pre-Sales - Check Technical Details' UNION ALL

SELECT 'B317','Mar 15 2013 12:45PM','310','SalesDirector',2,AgentServic,'Steve','NULL','Sales Director - Please Approve Proposal' UNION ALL

SELECT 'B317','Mar 15 2013 12:45PM','316','Technical',2,AgentServic,'John','Checked','Performed By System Administrator Account' UNION ALL

SELECT 'B317','Mar 13 2013 12:45PM','310','Technical',2,AgentServic,'John','NULL','Pre-Sales - Check Technical Details'

--===== Set the identity insert back to normal

SET IDENTITY_INSERT #mytable ON

Okay hopefully that has got things in a little more workable format

Little Explanation then.

Take B316 as an example going from earliest date, task is started and a line created by sending to John, John then rejects - this adds a new line and sends it to Fred, Fred Resubmits - this adds a new line and sends to John, John marks it as checked - this creates a new line and sends it to Steve and creates another line for Steve's task. In my "live" example, the Re-Submit task is not differentiated from the original task so would not say re-submit, it would just be null.

The result I want is

WorkflowID |DateStarted|PreSalesAgent|PreSalesDate|PreSalesOutcome|RejectAgent|ResubmitDate|ResubmitAgent|ResubmitOutcome|SalesDirector|SDOutcome|SDDate

B316 | 4-03-2013 | John | 15-03-2013 | Reject | Fred | 15-03-2013 | John | Checked | Steve | Null| Null

B317 | 3-03-2013 | John | 15-03-2013 | Checked | Null | Null | Null | Null | Steve | Null| Null

Hope that is now a bit clearer