SQL Pivot Query needs tuning

Crystal Rouse
Crystal Rouse used Ask the Experts™
on
I'm trying to write a SQL Pivot Query and can't get it quite right.  I feel like it's not grouping correctly.


CREATE VIEW [Report]
AS
SELECT

        [report].[Type],
      [table].Sales,
      [table].Service
      
      FROM [Report_Status] [report]
      
      FULL JOIN
      (
      Select

          Type,
          Min([Sales]) as Sales,
            Min([Service]) as Service
                        

      From
      (
            SELECT
            'Sales' [Header],
            [details].Type,
            [details].StatusCount            
            FROM Report_Status [details]            
            WHERE
              [details].[ManType] = 'Sales'

            UNION


            SELECT
            'Service' [Header],
            [details].Type,
            [details].[StatusCount]
            FROM Report_Status [details]            
            WHERE
               [details].[ManType] = 'Service'

            ) t1

            PIVOT

            (
                  MIN(StatusCount)
                  FOR Header IN ([Sales], [Service])
            )

            as PivotTable

            GROUP BY Type
            ) [table]

      ON [report].[Type] like  [table].[Type]
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
What database are you using ?
Crystal RouseTask Lead

Author

Commented:
I'm using a SQL Database.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Well, you are kind of fighting yourself with the group by - that is what the PIVOT is going to do.

Have a look at just the pivot first up :
      Select 

	  *

/*          Type,
          Min([Sales]) as Sales,
            Min([Service]) as Service
*/                        

      From 
      
           
		 (   SELECT
            'Sales' [Header],
            [details].Type,
            [details].StatusCount            
            FROM Report_Status [details]            
            WHERE 
              [details].[ManType] = 'Sales'

            UNION


            SELECT
            'Service' [Header],
            [details].Type,
            [details].[StatusCount]
            FROM Report_Status [details]            
            WHERE 
               [details].[ManType] = 'Service'

            ) t1

            PIVOT

            (
                  MIN(StatusCount)
                  FOR Header IN ([Sales], [Service])
            )

            as PivotTable

--            GROUP BY Type

Open in new window

So, what other columns do you want exposed ?
Then, why are we then selecting and joining to that result set ?
And dont really need union in there...
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Crystal RouseTask Lead

Author

Commented:
I really just need the type column to be the list on the left and Status Count be the values under the Headers (Sales and Service).
The individual queries give me the counts I need for the Status Count for the ManType of Sales and Service.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
I've gone through your queries and it actually doesn't require a UNION clause and then it can be simplified like this..
SELECT Type, Sales, Service
FROM (
SELECT [ManType],Type, StatusCount
FROM Report_Status [details]            
WHERE [details].[ManType] IN ('Sales', 'Service')) temp
PIVOT( MIN(StatusCount)
FOR ManType IN (Sales, Service)) as pvt
ORDER BY Type

Open in new window


Or else without PIVOT for 2 values like this..
SELECT DISTINCT Type, CASE WHEN ManType = 'Sales' THEN StatusCount END Sales, CASE WHEN ManType = 'Service' THEN StatusCount End Service 
FROM (
SELECT [ManType],Type, MIN(StatusCount) StatusCount
FROM Report_Status [details]            
WHERE [details].[ManType] IN ('Sales', 'Service')
GROUP BY ManType, Type
) temp

Open in new window

Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
OK, that is kind of what I was thinking too.

So let's work towards that goal....

The Pivot will do the aggregations and grouping for you. Unless you need column totals, there is no need for additional grouping.

In fact, you dont even need the Union.

For the example you have shown, you can do
select * 
from
( select [type],[ManType],[StatusCount]
  from [Report_Status]
  where [ManType] in ('Sales','Service')
) [Table]
PIVOT
( MIN(StatusCount) for [ManType] in ([Sales],[Service])
) PivotTable

Open in new window

Using that, we will find some NULL values, and so, instead of "SELECT *" we need to take control of the potential inclusion of NULL values. So lets change the "Select *"
SELECT [TYPE], ISNULL([Sales],0) as [SALES], ISNULL([Service],0) as [SERVICE]
FROM
   ( select [type],[ManType],[StatusCount]
     from [Report_Status]
     where [ManType] in ('Sales','Service')
   ) [Table]
PIVOT
   ( MIN(StatusCount) for [ManType] in ([Sales],[Service])
   ) PivotTable

Open in new window

Now, as a view
CREATE VIEW [vReport]
AS
SELECT [TYPE], ISNULL([Sales],0) as [SALES], ISNULL([Service],0) as [SERVICE]
FROM
   ( select [type],[ManType],[StatusCount]
     from [Report_Status]
     where [ManType] in ('Sales','Service')
   ) [Table]
PIVOT
   ( MIN(StatusCount) for [ManType] in ([Sales],[Service])
   ) [PivotTable]
GO

-- now to test our view

select * from vReport

Open in new window

Notice how I have given the view a different name - prefixed with a v

Also notice there is an inherent sort order as a result of the Pivot.

It would be remiss of me if I didnt point out that for this specific example, you dont really need a pivot and could  use a group by
select [type]
      ,MIN(case when [ManType] = 'Sales' then StatusCount else 0 end) as [Sales]
      ,MIN(case when [ManType] = 'Service' then StatusCount else 0 end) as [Service]
from [Report_Status]
where [ManType] in ('Sales','Service')
group by [type]

Open in new window

I have written articles about pivot which might help explain : https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
Crystal RouseTask Lead

Author

Commented:
Thank You!  This worked!  I want to try to add Totals now for each Type.  I'm going to read thru your article, I saved it in my Favorites!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Are they row totals ?

On the first select, you can simply add the column headers

SELECT [TYPE], ISNULL([Sales],0) as [SALES], ISNULL([Service],0) as [SERVICE], ISNULL([Sales],0) + ISNULL([Service],0) as [Total]

Open in new window

Crystal RouseTask Lead

Author

Commented:
I was able to get the Pivot working with Totals!  Thanks so much for the help.  I appreciate the detailed walk-thru.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial