rows to columns - creating view for SSRS

  • I think this issue is complex (at least to me) but will try to explain correctly and put some meaningful info in.

    I am trying to create a view to access from BIDS.

    The originating table goes like this

    wfIdcreated logType activityName agentSourceagentName outcome details

    C72013-03-14 310 TechnicalFred John NULL Pre-Sales - Check Technical Details

    C72013-03-15 316 TechnicalJohn Fred Reject Performed By John

    C72013-03-15 310 TechRejectJohn Fred NULL Originator - Technical Rejection

    C72013-03-16 316 TechRejectFred John NULL Performed By Fred

    C72013-03-16 310 TechnicalFred John NULL Pre-Sales - Check Technical Details

    C72013-03-16 316 TechnicalJohn Fred Checked Performed By John

    C72013-03-16 310 SalesDirectorJohn Steve NULL Sales Director - Please Approve Proposal

    C82013-03-14 310 TechnicalFred John NULL Pre-Sales - Check Technical Details

    C82013-03-15 316 TechnicalJohn Fred Checked Performed By John

    C82013-03-15 310 SalesDirectorJohn Steve NULL Sales Director - Please Approve Proposal

    I am trying to return the following in the view

    workflowId TechDt TechAgent TechOut TechOutDt TechDur ReSubDt ReSubAgent ReSubOut ReSubOutDt ResubDur SDAgent SDOut SDDur

    C7 2013-03-14 John Reject 2013-03-15 1 2013-03-16 John Checked 2013-03-16 0 Steve NULL NULL

    C8 2013-03-14 John Checked 2013-03-15 1 NULL NULL NULL NULL NULL Steve NULL NULL

    Where Dt = Date, Out = Outcome, Dur = Duration

    For this example, assume there can only be one re-submit and the only outcome for SalesDirector is Accept or Reject with no resubmit n the SalesDirector outcome.

    In words, I am trying to get the duration of each step of the workflow, in the real database there are many possible legs to a workflow and many possible routes but they all follow the same basic principal. I would like to be able to see where the current WorkflowID is, for example C8 and C7 is currently with the Sales Director.

    I have tried unions and tried inner joins to get the data onto one line by doing an inner join then using "where" in each join to separate out the different steps and get them into columns, my biggest issue is that the second workflow submission is the same as the original submission and I need to identify it as a different one. The secondary issue is I don't know which method would run fastest. The actual database has around 40 000 records per year and around 8 lines per unique workflowId.

    If this is not explained well, please tell me and I will attempt to make it more clear

  • It is pretty tough to offer much help because the details are not very clear. It would help greatly if you could post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. This way we can work on helping you code a query for your problem. Please take a look at the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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

  • 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.

    B316 | 4-03-2013

    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?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hi. Thank you so much for your effort.

    I was trying to rush things before getting removed from my office, Fridays tend to be nuts at knock off time and not much appetite for waiting around while i get things finished.

    The start date is the same as the created date on first line for each Workflow id, sorry for not noting that.

    I do not spend enough time working on sql and only get to work on it just a few times a year, hopefully this will be the start of a bit of a run and make some more reading worthwhile.

    I'll hopefully be able to get in remotely and will be able to test the script out as well as drop the db onto my laptop.

  • steven.n.rhodes (3/15/2013)


    Hi. Thank you so much for your effort.

    I was trying to rush things before getting removed from my office, Fridays tend to be nuts at knock off time and not much appetite for waiting around while i get things finished.

    The start date is the same as the created date on first line for each Workflow id, sorry for not noting that.

    I do not spend enough time working on sql and only get to work on it just a few times a year, hopefully this will be the start of a bit of a run and make some more reading worthwhile.

    I'll hopefully be able to get in remotely and will be able to test the script out as well as drop the db onto my laptop.

    No worries. The first two columns are easy enough but it is extremely vague from there what the business rules might be.

    Here is the first couple columns.

    select WorkflowID, min(created) as DateStarted

    from #mytable

    group by WorkflowID

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 6 posts - 1 through 5 (of 5 total)

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