You got the basic gist of what we need but you certainly didn't test it. There were a lot of issues here.

WorkflowId defined a uniqueidentifier but the values are all char(4).

Setting IDENTITY_INSERT ON/OFF - there is no identity on the table.

AgentServic was not wrapped as string.

Not really sure why you are using sql_variant but that is a topic for another day.

I cleaned this up and it will now at least run. Can you check this to see if this will work?

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] char(4) 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

select * from #mytable

Now I realize it may seem like I am being a bit anal but the real issue is that your desired output doesn't seem to match up with the sample data.

You have that listed as the DateStarted but regardless of dateformat (mdy or dmy) there is no date in the table for B316 with that date. Where does it come from?