Solved

Adding a "calculated" field to a pivot view

Posted on 2012-04-11
21
310 Views
Last Modified: 2012-04-16
Hello,

I have a view that displays the status of a file along 8 different review tracks.  For example

USI      Review1      Status1     Review2        Status2           Review3                Status3 ......................
234         FRS          Pending     QA               Pending           Senior Review      Missing Docs

The view is working fine.  However, the client wants me to add another field called GFP (global file position)  GFP tells the user what stage the  file is in based on the weight of the least weighted status.  I've managed to create a field for each status (MS1, MS2) that give the weight for a particular status.  So for example, Pending has a weight of 1 while missing docs has a weight of 3 so the GFP would be "Pending" for that file (USI 234)

USI      GFP         Review1     MS1      Status1        Review2       MS2      Status2                          
234     Pending      FRS           3        MissingDocs     QA                1           Pending  

I am trying to figure out how to Populate the GFP Field.  I'm at home right now but I will post the code for the pivot.  It's a pretty standard pivot.  So I think what I am trying to do is create a type of calculated field.  I would appreciate any insights anyone might have.  I'm going to sleep on it.   This is on a SQL server 2005 backend.
0
Comment
Question by:chtullu135
  • 14
  • 7
21 Comments
 

Author Comment

by:chtullu135
ID: 37837383
Hello,
After getting a good nights sleep, I've refined the question.  Basically I want to set GFP to the status that has the lowest weight which in the above example is "Pending" with a weight of 1.  I think the best way to do this is to create a udf that returns this status and to place this udf in the select clause as in SELECT  udf_ReturnLowestStatus AS GFP From PivotTable.  I'm thinking this might be the best way to go.  I'd like to get some opinions.

CREATE udf_ReturnLowestStatus
@Status1 as varchar(500),
@Status2 as varchar(500),
@Status3 as varchar(500),
@Status4 as varchar(500),
@Status5 as varchar(500),
@Status6 as varchar(500),
@Status7 as varchar(500),
@Status8 as varchar(500),
@MS1 as int,
@MS2 as int,
@MS3 as int,
@MS4 as int,
@MS5 as int,
@MS6 as int,
@MS7 as int,
@MS8 as int
AS

--Return the status
0
 

Author Comment

by:chtullu135
ID: 37837797
Here is the code for the calling procedure

SELECT p3.USI,  Max(WS.Workstream) AS WorkStream,

udf_ReturnLowestStatus(Status1, Status2, Status3, Status4, Status5, Status6, Status7, Status8,
MappedStatus1, MappedStatus2, MappedStatus3, MappedStatus4, MappedStatus5, MappedStatus6, MappedStatus7, MappedStatus8) AS [GFP],

MAX([Review1]) AS [Review1],  MAX([Status1]) AS [Status1],  MAX([MappedStatus1]) AS [MappedStatus1],
MAX([Review2]) AS [Review2],  MAX([Status2]) AS [Status2],  MAX([MappedStatus2]) AS [MappedStatus2],
MAX([Review3]) AS [Review3],  MAX([Status3]) AS [Status3],  MAX([MappedStatus3]) AS [MappedStatus3],
MAX([Review4]) AS [Review4],  MAX([Status4]) AS [Status4],  MAX([MappedStatus4]) AS [MappedStatus4],
MAX([Review5]) AS [Review5],  MAX([Status5]) AS [Status5],  MAX([MappedStatus5]) AS [MappedStatus5],
MAX([Review6]) AS [Review6],  MAX([Status6]) AS [Status6],  MAX([MappedStatus6]) AS [MappedStatus6],
MAX([Review7]) AS [Review7],  MAX([Status7]) AS [Status7],  MAX([MappedStatus7]) AS [MappedStatus7],
MAX([Review8]) AS [Review8],  MAX([Status8]) AS [Status8],      MAX([MappedStatus8]) AS [MappedStatus8],
MAX(AnalystName) AS AnalystName, MAX(AnalystCompletionDate) AS AnalystCompletionDate, MAX([Senior Analyst]) AS 'Senior Analyst', MAX(SeniorCompletionDate) AS SeniorCompletionDate, MAX(QAAnalyst) AS QAAnalyst, MAX(QAConfirmationDate) AS QAConfirmationDate, SUM(ExceptionCount) AS ExceptionCount
FROM (
SELECT      USI, Review, ReviewStatus, MappedStatus, 'MappedStatus'+CONVERT(VARCHAR(2),rn) MappedStatusPvtCol,'Review'+CONVERT(VARCHAR(2),rn) ReviewPvtCol, 'Status'+CONVERT(VARCHAR(2),rn) StatusPvtCol,
            AnalystName, AnalystCompletionDate, [Senior Analyst], SeniorCompletionDate, QAAnalyst, QAConfirmationDate, ExceptionCount
FROM      (SELECT USI, Review, ReviewStatus, MappedStatus,
            AnalystName, AnalystCompletionDate, [Senior Analyst], SeniorCompletionDate, QAAnalyst, QAConfirmationDate, ExceptionCount,
            ROW_NUMBER() OVER (PARTITION BY USI ORDER BY Review) rn FROM  dbo.vwViewAllForPivot) AS SourceTable
) p
PIVOT
(
max(Review)
FOR ReviewPvtCol IN ([Review1], [Review2], [Review3], [Review4], [Review5], [Review6], [Review7], [Review8])
) AS P1
PIVOT
(
max(ReviewStatus)
FOR StatusPvtCol IN ([Status1], [Status2], [Status3], [Status4], [Status5], [Status6], [Status7], [Status8])
) AS P2
PIVOT
(
max(MappedStatus)
FOR MappedStatusPvtCol IN ([MappedStatus1], [MappedStatus2], [MappedStatus3], [MappedStatus4], [MappedStatus5], [MappedStatus6], [MappedStatus7], [MappedStatus8])
) AS P3
INNER JOIN dbo.vwWorkstream WS
ON P3.USI = WS.USI
GROUP BY P3.USI
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
0
 

Author Comment

by:chtullu135
ID: 37839081
Hello,

I came up with a solution.  It works but it's a little brute force.  I'm open to any suggestions

ALTER VIEW [dbo].[vwPivotedReviewsRevised]
AS
SELECT p3.USI,  Max(WS.Workstream) AS WorkStream,
CASE
      WHEN MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus4]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus5]), 9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus1]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status1])
      WHEN MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus4]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus5]), 9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus2]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status2])
      WHEN MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus4]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus5]), 9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus3]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status3])
      WHEN MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus5]), 9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus4]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status4])      
      WHEN MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus4]), 9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus5]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status5])            
      WHEN MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus4]), 9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus5]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus7]),9999) AND MAX([MappedStatus6]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status6])            
      WHEN MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus4]), 9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus5]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus7]) <= ISNULL(MAX([MappedStatus8]), 9999) THEN MAX([Status7])            
      WHEN MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus1]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus2]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus3]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus4]), 9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus5]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus6]),9999) AND MAX([MappedStatus8]) <= ISNULL(MAX([MappedStatus7]), 9999) THEN MAX([Status8])
END AS [GFP],
--CASE
--      
--      WHEN MAX(Review8) IS NOT NULL THEN MAX(Status8)
--      WHEN MAX(Review7) IS NOT NULL THEN MAX(Status7)
--      WHEN MAX(Review6) IS NOT NULL THEN MAX(Status6)
--      WHEN MAX(Review5) IS NOT NULL THEN MAX(Status5)
--      WHEN MAX(Review4) IS NOT NULL THEN MAX(Status4)
--      WHEN MAX(Review3) IS NOT NULL THEN MAX(Status3)
--      WHEN MAX(Review2) IS NOT NULL THEN MAX(Status2)
--      WHEN MAX(Review1) IS NOT NULL THEN MAX(Status1)            
--END As [GFP],
MAX([Review1]) AS [Review1],  MAX([Status1]) AS [Status1],  MAX([MappedStatus1]) AS [MappedStatus1],
MAX([Review2]) AS [Review2],  MAX([Status2]) AS [Status2],  MAX([MappedStatus2]) AS [MappedStatus2],
MAX([Review3]) AS [Review3],  MAX([Status3]) AS [Status3],  MAX([MappedStatus3]) AS [MappedStatus3],
MAX([Review4]) AS [Review4],  MAX([Status4]) AS [Status4],  MAX([MappedStatus4]) AS [MappedStatus4],
MAX([Review5]) AS [Review5],  MAX([Status5]) AS [Status5],  MAX([MappedStatus5]) AS [MappedStatus5],
MAX([Review6]) AS [Review6],  MAX([Status6]) AS [Status6],  MAX([MappedStatus6]) AS [MappedStatus6],
MAX([Review7]) AS [Review7],  MAX([Status7]) AS [Status7],  MAX([MappedStatus7]) AS [MappedStatus7],
MAX([Review8]) AS [Review8],  MAX([Status8]) AS [Status8],      MAX([MappedStatus8]) AS [MappedStatus8],
MAX(AnalystName) AS AnalystName, MAX(AnalystCompletionDate) AS AnalystCompletionDate, MAX([Senior Analyst]) AS 'Senior Analyst', MAX(SeniorCompletionDate) AS SeniorCompletionDate, MAX(QAAnalyst) AS QAAnalyst, MAX(QAConfirmationDate) AS QAConfirmationDate, SUM(ExceptionCount) AS ExceptionCount
FROM (
SELECT      USI, Review, ReviewStatus, MappedStatus, 'MappedStatus'+CONVERT(VARCHAR(2),rn) MappedStatusPvtCol,'Review'+CONVERT(VARCHAR(2),rn) ReviewPvtCol, 'Status'+CONVERT(VARCHAR(2),rn) StatusPvtCol,
            AnalystName, AnalystCompletionDate, [Senior Analyst], SeniorCompletionDate, QAAnalyst, QAConfirmationDate, ExceptionCount
FROM      (SELECT USI, Review, ReviewStatus, MappedStatus,
            AnalystName, AnalystCompletionDate, [Senior Analyst], SeniorCompletionDate, QAAnalyst, QAConfirmationDate, ExceptionCount,
            ROW_NUMBER() OVER (PARTITION BY USI ORDER BY Review) rn FROM  dbo.vwViewAllForPivot) AS SourceTable
) p
PIVOT
(
max(Review)
FOR ReviewPvtCol IN ([Review1], [Review2], [Review3], [Review4], [Review5], [Review6], [Review7], [Review8])
) AS P1
PIVOT
(
max(ReviewStatus)
FOR StatusPvtCol IN ([Status1], [Status2], [Status3], [Status4], [Status5], [Status6], [Status7], [Status8])
) AS P2
PIVOT
(
max(MappedStatus)
FOR MappedStatusPvtCol IN ([MappedStatus1], [MappedStatus2], [MappedStatus3], [MappedStatus4], [MappedStatus5], [MappedStatus6], [MappedStatus7], [MappedStatus8])
) AS P3
INNER JOIN dbo.vwWorkstream WS
ON P3.USI = WS.USI
GROUP BY P3.USI
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 37840691
Have you got some sample data in say an Excel Spreadsheet of vwViewAllForPivot and vwWorkStream ?

Dont want any live or sensitive information, just something representative.

I am pretty sure I can help you, but being involved, would like to run up a test and see the best way...

The challenge as I understand it is that there can be multiple weights and we want the "least" real weight to show. I am not so sure I would do that big case statement, rather, try to resolve at a lower level so we can simply get the MIN(mappedstatus) as GFP.

But, might also need a little more explanation about those weights (e.g. 1 = pending where 1 is from mappedstatus* etc). And understand the need to manage the possibility of NULLS because there might not be a mappedstatus for a section.

So, if you are able to post some sample data we can get something happening quicker. If you cannot, then we can still get something happening, might just take a little longer to do so.
0
 

Author Comment

by:chtullu135
ID: 37840774
Sure thing mark.  I'll do it tomorrow
0
 

Author Comment

by:chtullu135
ID: 37844353
Hello Mark,

Attached is a excel file with two tabs, one for the ViewAllForPivot data and the other containing the workstream data
SampleViewForPivot.xlsx
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 37844840
Thanks,

Looks like I only have the 1 review for each USI... Would it be possible to add a couple more in ?

If it is OK for me to simply copy and paste rows and make the copied rows a little different, then thats fine too.

By the looks I would only have to add a few rows into the first worksheet and not upset the fabric of space time continuim in the second 'workstream'  worksheet...
0
 

Author Comment

by:chtullu135
ID: 37845030
It's okay to add more rows as long as the USI remains unique
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 37845151
Hmmm... Was going to repeat USI otherwise how do we get multiple reviews for each USI ? I realise that they must be distinct (ie only use the ones already there).

Will have a play anyway. But it is nearly time to take the kids to Soccer, so wont be back for a couple of hours. Oh, the pressures of being "coach" :)
0
 

Author Comment

by:chtullu135
ID: 37845473
I'm sorry,

Actually there are multiple usi's in the viewforallpivot.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 37847840
OK,

Couple of little challenges, like no MappedStatus in the spreadsheet. But assume it is a simple lookup, similar with statusID and the Status Name - can you please confirm ?

Also, the MAX() aggregations dont necessarily make sense in context. For example, the max([senior Analyst]) doesnt necessarily mean it corresponds to the max(seniorcompletiondate) and similar for Analyst and QA.

I have written code for the above scenarios, and also matching what you have (well, nearly, using statusweight in place of mapped status for the moment).

Anyway, if you could please clarify... And in the meantime, have a look at (and you need to replace # with dbo. for the table names)

It also introduces some new concepts like window functions for aggregations. And that is the real reason for the code as it is to demonstrate to you some new techniques (the code itself is not quite 100% in the example below).


;with Reviews as
(SELECT P.USI, WS.Workstream, P.Review, P.ReviewStatus, P.StatusID, convert(varchar(20),P.StatusWeight) as MappedStatus,
        convert(varchar(2),ROW_NUMBER() OVER (PARTITION BY P.USI ORDER BY P.Review)) rn,
        min(P.statusID) OVER (partition by P.USI) GFP,
        max(isnull(P.AnalystName,'')) over (partition by P.USI) AnalystName, 
        max(isnull(P.AnalystCompletionDate,'')) over (partition by P.USI) AnalystCompletionDate, 
        max(isnull(P.[Senior Analyst],'')) over (partition by P.USI) SeniorAnalyst, 
        max(isnull(P.SeniorCompletionDate,'')) over (partition by P.USI) SeniorCompletionDate, 
        max(isnull(P.QAAnalyst,'')) over (partition by P.USI) QAAnalyst,
        max(isnull(P.QAConfirmationDate,'')) over (partition by P.USI) QAConfirmationDate,
        sum(isnull(P.ExceptionCount,0)) over (partition by P.USI) ExceptionCount
 FROM  #vwViewAllForPivot P
 INNER JOIN #vwWorkstream WS on P.USI = WS.USI )

SELECT *
from  (   
       Select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'Review'+rn PvtCol, Review as PvtVal
       FROM   Reviews
       UNION 
       Select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'Status'+rn PvtCol, ReviewStatus as PvtVal
       FROM   Reviews
       UNION
       select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'MappedStatus'+rn PvtCol, MappedStatus PvtVal
       FROM   Reviews
      ) src
pivot

( max(pvtval) for PvtCol in ([review1],[status1],[mappedstatus1]
                            ,[review2],[status2],[mappedstatus2]
                            ,[review3],[status3],[mappedstatus3]
                            ,[review4],[status4],[mappedstatus4]
                            ,[review5],[status5],[mappedstatus5]
                            ,[review6],[status6],[mappedstatus6]
                            ,[review7],[status7],[mappedstatus7]
                            ,[review8],[status8],[mappedstatus8])) pvt

Open in new window

0
 

Author Comment

by:chtullu135
ID: 37848316
MappedStatus is now StatusWeight.  They are both the same.  I thought that statusweight was a more accurate description
0
 

Author Comment

by:chtullu135
ID: 37848322
Hello Mark,

I will give the code a test tomorrow.  I'll go ahead and read up on windows functions for aggregations.  I am in a Sql Server 2005 environment.  Will that make a difference.


Juan
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 37849417
It will work fine in SQL2005. They are even better / more powerful in the more recent releases (like SQL2012).

You arent concerned about the MAX() ? When looking at it again, I realised that maybe those columns are only populated (once) at the appropriate time (or status), so, if there is only a value and not multiple values for a given USI in those columns, then MAX() will work fine.

A lot of your query performance will be very dependant on indexes. I see that it will be a view on top of another view.

Need to be a little bit careful when doing that so the underlying table indexes are still available as obvious choices for the optimizer. Sometimes views have complex joins or selection criteria so it can result in the optimizer choosing to do a scan rather than using an index.

I will play a bit with different queries to see their relative performance.
0
 

Author Comment

by:chtullu135
ID: 37853373
I ran a test using a select statment against each view.  My original view took 10 second while the one above took about 30 seconds
0
 

Author Comment

by:chtullu135
ID: 37853376
Here is the test script
Create View vwTEST
AS

with Reviews as
(SELECT P.USI, WS.Workstream, P.Review, P.ReviewStatus, P.StatusID, convert(varchar(20),P.StatusWeight) as MappedStatus,
        convert(varchar(2),ROW_NUMBER() OVER (PARTITION BY P.USI ORDER BY P.Review)) rn,
        min(P.statusID) OVER (partition by P.USI) GFP,
        max(isnull(P.AnalystName,'')) over (partition by P.USI) AnalystName,
        max(isnull(P.AnalystCompletionDate,'')) over (partition by P.USI) AnalystCompletionDate,
        max(isnull(P.[Senior Analyst],'')) over (partition by P.USI) SeniorAnalyst,
        max(isnull(P.SeniorCompletionDate,'')) over (partition by P.USI) SeniorCompletionDate,
        max(isnull(P.QAAnalyst,'')) over (partition by P.USI) QAAnalyst,
        max(isnull(P.QAConfirmationDate,'')) over (partition by P.USI) QAConfirmationDate,
        sum(isnull(P.ExceptionCount,0)) over (partition by P.USI) ExceptionCount
 FROM  dbo.vwViewAllForPivot P
 INNER JOIN dbo.vwWorkstream WS on P.USI = WS.USI )

SELECT *
from  (  
       Select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'Review'+rn PvtCol, Review as PvtVal
       FROM   Reviews
       UNION
       Select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'Status'+rn PvtCol, ReviewStatus as PvtVal
       FROM   Reviews
       UNION
       select USI,WorkStream,GFP,AnalystName,AnalystCompletionDate,SeniorAnalyst,SeniorCompletionDate,QAAnalyst,QAConfirmationDate,ExceptionCount,
              'MappedStatus'+rn PvtCol, MappedStatus PvtVal
       FROM   Reviews
      ) src
pivot

( max(pvtval) for PvtCol in ([review1],[status1],[mappedstatus1]
                            ,[review2],[status2],[mappedstatus2]
                            ,[review3],[status3],[mappedstatus3]
                            ,[review4],[status4],[mappedstatus4]
                            ,[review5],[status5],[mappedstatus5]
                            ,[review6],[status6],[mappedstatus6]
                            ,[review7],[status7],[mappedstatus7]
                            ,[review8],[status8],[mappedstatus8])) pvt
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 37854002
Yep, that is possible. It really depends on the underlying indexes, and of course volumes of data.

We could dig deeper, but would need to start looking at the vwallforpivot view and what it is doing.

10 seconds is still a fairly long time, meaning 30 seconds is unacceptably long.

So, up to you at this point. Dig deeper or be happy with 10 seconds ? Your query while lots of words does work and works reasonably well...
0
 

Author Comment

by:chtullu135
ID: 37854212
I've requested that this question be closed as follows:

Accepted answer: 0 points for chtullu135's comment #37853376

for the following reason:

Although I chose to use the my original solution, I appreciate the time and effort you took. &nbsp;In addition, it gave other ways of looking at pivots
0
 

Author Comment

by:chtullu135
ID: 37854213
I want to cancel my request.  I meant to award points
0
 

Author Closing Comment

by:chtullu135
ID: 37854216
Thank you for your time and effort.  Although I will be using my original solution, I found the information you presented very interesting
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 37854298
Well, thankyou sir, much appreciated.

And if you do want to delve deeper, you can check out contact details on my bio...
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now