Adding a "calculated" field to a pivot view

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.
Juan VelasquezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Juan VelasquezAuthor Commented:
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
Juan VelasquezAuthor Commented:
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
Juan VelasquezAuthor Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Mark WillsTopic AdvisorCommented:
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
Juan VelasquezAuthor Commented:
Sure thing mark.  I'll do it tomorrow
0
Juan VelasquezAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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
Juan VelasquezAuthor Commented:
It's okay to add more rows as long as the USI remains unique
0
Mark WillsTopic AdvisorCommented:
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
Juan VelasquezAuthor Commented:
I'm sorry,

Actually there are multiple usi's in the viewforallpivot.
0
Mark WillsTopic AdvisorCommented:
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
Juan VelasquezAuthor Commented:
MappedStatus is now StatusWeight.  They are both the same.  I thought that statusweight was a more accurate description
0
Juan VelasquezAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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
Juan VelasquezAuthor Commented:
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
Juan VelasquezAuthor Commented:
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
Mark WillsTopic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor Commented:
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
Juan VelasquezAuthor Commented:
I want to cancel my request.  I meant to award points
0
Juan VelasquezAuthor Commented:
Thank you for your time and effort.  Although I will be using my original solution, I found the information you presented very interesting
0
Mark WillsTopic AdvisorCommented:
Well, thankyou sir, much appreciated.

And if you do want to delve deeper, you can check out contact details on my bio...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.