SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PIVOT working incorrectly?


PIVOT working incorrectly?

Author
Message
Ryan Clare
Ryan Clare
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 66
Comments posted to this topic are about the item PIVOT working incorrectly?
Calvin Lawson
Calvin Lawson
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1206 Visits: 102
Interesting, thanks for the tip; I'll have to watch out for that. So it seems to result in a sum(distinct value) instead of sum(value), right?

It does seem like pre-aggregating the data is the key, but I wouldn't use a temp table and an update, I'd just use a derived table or view, then aggregate the results as well:




select salesPersonsName
, sum([Jan]) as [Jan]
, sum([Feb]) as [Feb]
, sum(SaverPoints) as SaverPoints
from
( select
salesPersonsName
, sum(numberOfSales) as NumberOfSales
, monthAbv
, sum(saverPoints) as saverPoints
from #tmpPivotTest
group by
salesPersonsName
, numberOfSales
, monthAbv
) as SourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan],[Feb]) )as PivotTable
group by salesPersonsName





Signature is NULL
JamieBates
JamieBates
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 3
I am having difficulty deciphering the example given. Would you mind doing another Pivot Table query using Northwind Traders? That structure may prove easier to understand the syntax of this strategy.

Jamie
benjamin.nelson06
benjamin.nelson06
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 82
Very interesting as I have been working on doing a similar thing now for a few days. However I needed a Grand Total Column and a Grand Total Row for reporting purposes. I also did not know what the PivotValues would be therefore this all had to be done with dynamic SQL. I too created a temporary table in order to store the data before I pivoted the data. Here is how I did it (Please excuse the smiley's as they automatically were added. where you see a smily it's just a close paren.)

--Get the Pivot Columns by querying the distinct [Month] & Year values from the query
Declare @PivotValues as VARCHAR(8000)
Declare @PivotSum as VARCHAR(8000)
Declare @PivotTotal as VARCHAR(8000)
Declare @PivotTypes as VARCHAR(8000)
Declare @MonthYear as VARCHAR(25)
Declare @EDATEMM as INT
Declare @Year as INT

--Loop over the @YearMonth CURSOR table to build our PivotField Values
DECLARE YearMonth_cursor CURSOR FOR
SELECT DISTINCT dbo.MonthMst.[Month] + '-' + CAST(Year([EDATE]) as varchar(4)), EDATEMM, Year([EDATE])
FROM dbo.MonthMst INNER JOIN
@MasterSubset AS MS ON dbo.MonthMst.cLngMonthCode = MS.EDATEMM
Order By Year([EDATE]), EDATEMM

OPEN YearMonth_cursor

FETCH NEXT FROM YearMonth_cursor
INTO @MonthYear, @EDATEMM, @Year

WHILE @@FETCH_STATUS = 0
BEGIN
--Build the PivotValues to be used in forming the Pivot Statement
If COALESCE(@PivotValues, '') = ''
SET @PivotValues = '[' + @MonthYear + ']'
ELSE
SET @PivotValues = @PivotValues + ',[' + @MonthYear + ']'

--Build the PivotTotal for use in forming the Grand Total Column
If COALESCE(@PivotTotal, '') = ''
SET @PivotTotal = 'COALESCE([' + @MonthYear + '],0)'
ELSE
SET @PivotTotal = @PivotTotal + ' + COALESCE([' + @MonthYear + '],0)'

--Build the PivotSum for use in forming the Grand Total Row
If COALESCE(@PivotSum, '') = ''
SET @PivotSum = 'SUM(COALESCE([' + @MonthYear + '],0))'
ELSE
SET @PivotSum = @PivotSum + ', SUM(COALESCE([' + @MonthYear + '],0))'

--Build the PivotTypes for use in declaring a Table Variable
If COALESCE(@PivotTypes, '') = ''
SET @PivotTypes = '[' + @MonthYear + '] INT NULL'
ELSE
SET @PivotTypes = @PivotTypes + ', [' + @MonthYear + '] INT NULL'

-- Get the next YearMonth.
FETCH NEXT FROM YearMonth_cursor
INTO @MonthYear, @EDATEMM, @Year

END

--Add the Grand Total Column to the PivotValues
--This keeps us from having to modify the Structure in order to add
--The Grand Total column
set @PivotValues = @PivotValues + ',[Grand Total]'

--We will need to SUM the Grand Total Column in order to build the last value Grand Total
--Row
set @PivotSum = @PivotSum + ', SUM([Grand Total])'

--Add the Grand Total column to our PivotTypes as this needs to be a column in our Table Variable
set @PivotTypes = @PivotTypes + ',[Grand Total] INT NULL'

CLOSE YearMonth_cursor
DEALLOCATE YearMonth_cursor

DECLARE @SQL as NVARCHAR(4000)

--Stuff our Data into a Temporary table
--to be used in the dynamic SQL below
Select [Month] + '-' + CAST([Year] as varchar(4)) AS MonthYear, GroupCol1, GroupCol2, GroupCol3, AggregatCol
INTO #PivotData
from
(Select Month, Year, GroupCol1, GroupCol2, GroupCol3, AggregatCol From DataTable) as DT

--Now here was the tricky part. Since I didn't know what my pivot columns would be I needed
--to build dynamic SQL however I couldn't figure out how to get the data to return properly. So
--I basically created a dynamic stored proc without actually creating a stored proc.
SET @SQL = 'DECLARE @PivotTable1 TABLE( GroupCol1 varchar(50) NOT NULL,
GroupCol2 varchar(20) NOT NULL,
GroupCol3 varchar(20) NOT NULL,
' + @PivotTypes + ');
INSERT INTO @PivotTable1 (GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + ' )
Select GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + '
FROM
(Select GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + '
FROM
(
Select MonthYear,GroupCol1,GroupCol2,
GroupCol3, AggregateCol
from #PivotData
) As Source
PIVOT
(
SUM(Source.AggregateCol)
FOR MONTHYEAR IN (' + @PivotValues + ')
) As PivotTable) As PT;
Update @PivotTable1 Set [Grand Total] = ' + @PivotTotal +
'; Insert Into @PivotTable1 (GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + ')
Select ''Grand Total'', '''', '''', ' + @PivotSum + ' From @PivotTable1;
Select * From @PivotTable1;'

Exec sp_executesql @sql

Drop Table #PivotData

And here are the results:
GroupCol1 GroupCol2 GroupCol3 January-2007 February-2007 Grand Total
-------------------------------------------------------------------------------------------------
GroupValue1 Group2Value1 Group3Value1 391 356 747
GroupValue2 Group2Value2 Group3Value1 356 419 775
GroupValue3 Group2Value3 Group3Value1 181 181 362
Grand Total 928 956 1884
samir-424465
samir-424465
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 83
The working of pivot depends upon the columns we specify in the select list. The pivot has one column on which aggregate function is applied (like sum(),count(), etc), the second column from which the data is rotated and pivot implicitly applies GROUP BY clause on rest of columns.

For E.g :

Select objid, attr1, attr2, Col_data1, Col_data2
from
(
Select num, objid, attr1, attr2, attr3 from Table_Name
) as Raw_data

pivot

(
sum(num) for attr3 in (Col_data1, Col_data2)
) as pvt

In this case, the aggregate function sum() is applied on column "num". Col_data1 and Col_data2 are the row values which will form new columns in the output table. Now the pivot implicitly applies GROUP BY to all those columns in Select list which do not take part in PIVOT, in this case objid, attr1, attr2. So we need to be careful in selecting the columns. So use a derived query to select the required columns instead of using "Select * from table_name". Since it applies GROUP BY in this fashion we might end up applying GROUP BY to all those columns which we dont want resulting in wrong results as mentioned in this post .
mpadierna1
mpadierna1
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 9
Interesting article, as usual, but that class="code" style trashes the page, making it unreadable, please correct it! thanks
Matt Marston
Matt Marston
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2001 Visits: 412
The PIVOT statement can be useful at times, but it is handy to know that anything that can be done with a PIVOT statement can be converted to an equivalent query without the PIVOT statement.

Here is how:
1. All non-pivoted columns from the source query are put in a GROUP BY clause (e.g. GROUP BY salesPersonsName)
2. Create a case statement for each pivoted column value (e.g. Jan, Feb) that returns the column to be aggregated when the column to be pivoted matches the value. For example

CASE WHEN monthAbv = 'Jan' THEN numberOfSales END


3. Wrap each case statement in the desired aggregate function and add the column alias. Note that this also gives you flexibility to name the columns whatever you want. For example

SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS [Jan]



For more details and more examples see the comments in the script below.

-- The initial PIVOT query:
select salesPersonsName, [Jan], [Feb] from
(select salesPersonsName, numberOfSales, monthAbv from #tmpPivotTest) as SourceTable
--------^ salesPersonsName is the only non-pivoted column from the source;
-------- so we GROUP BY salesPersonsName
PIVOT (sum(numberOfSales) for monthAbv in ([Jan],[Feb]) )as PivotTable
-------^ sum is the Aggregate function ----^ Jan and Feb are the values to use in the case statements

-- The equivalent CASE/GROUP BY query:
SELECT
salesPersonsName,
SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS Jan,
SUM(CASE WHEN monthAbv = 'Feb' THEN numberOfSales END) AS Feb
FROM #tmpPivotTest
GROUP BY salesPersonsName

-- Pivot table query trying to get saver points as well:
select salesPersonsName, [Jan], [Feb], saverPoints from
(select * from #tmpPivotTest) as SourceTable
--------^ salesPersonsName and saverPoints are both non-pivoted columns returned by the source query;
-------- so we GROUP BY salesPersonsName, saverPoints
PIVOT (sum(numberOfSales) for monthAbv in ([Jan], [Feb]) )as PivotTable
-------^ sum is the Aggregate function ----^ Jan and Feb are the values to use in the case statements

-- The equivalent CASE/GROUP BY query:
SELECT
salesPersonsName,
SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS Jan,
SUM(CASE WHEN monthAbv = 'Feb' THEN numberOfSales END) AS Feb,
saverPoints
FROM #tmpPivotTest
GROUP BY salesPersonsName, saverPoints

-- The desired results with the sum of saverPoints using CASE/GROUP BY
SELECT
salesPersonsName,
SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS Jan,
SUM(CASE WHEN monthAbv = 'Feb' THEN numberOfSales END) AS Feb,
SUM(saverPoints) AS saverPoints
FROM #tmpPivotTest
GROUP BY salesPersonsName

-- Note that CASE/GROUP BY is more flexible.
-- PIVOT only allows one source column to be aggregated.
-- For example, to aggregate both sales and points by month:
SELECT
salesPersonsName,
SUM(CASE WHEN monthAbv = 'Jan' THEN numberOfSales END) AS [Jan Sales],
SUM(CASE WHEN monthAbv = 'Jan' THEN saverPoints END) AS [Jan Points],
SUM(CASE WHEN monthAbv = 'Feb' THEN numberOfSales END) AS [Feb Sales],
SUM(CASE WHEN monthAbv = 'Feb' THEN saverPoints END) AS [Feb Points]
FROM #tmpPivotTest
GROUP BY salesPersonsName

-- In order to do the equivalent of the above query you would need to join two PIVOT
-- statements, which is less efficient because it queries the source table twice.
;with SalesPivotResults as
(
select salesPersonsName, [Jan] as [Jan Sales], [Feb] as [Feb Sales]
from (select salesPersonsName, numberOfSales, monthAbv from #tmpPivotTest)as SalesSourceTable
PIVOT (sum(numberOfSales) for monthAbv in ([Jan],[Feb]) )as SalesPivotTable
), PointsPivotResults as
(
select salesPersonsName, [Jan] as [Jan Points], [Feb] as [Feb Points]
from (select salesPersonsName, saverPoints, monthAbv from #tmpPivotTest)as PointsSourceTable
PIVOT (sum(saverPoints) for monthAbv in ([Jan],[Feb]) )as PointsPivotTable
)
select SalesPivotResults.salesPersonsName, [Jan Sales], [Jan Points], [Feb Sales], [Feb Points]
from SalesPivotResults
INNER JOIN PointsPivotResults ON PointsPivotResults.salesPersonsName = SalesPivotResults.salesPersonsName


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116619 Visits: 41430
benjamin.nelson06 (5/13/2008)
Very interesting as I have been working on doing a similar thing now for a few days. However I needed a Grand Total Column and a Grand Total Row for reporting purposes. I also did not know what the PivotValues would be therefore this all had to be done with dynamic SQL. I too created a temporary table in order to store the data before I pivoted the data. Here is how I did it (Please excuse the smiley's as they automatically were added. where you see a smily it's just a close paren.)

--Get the Pivot Columns by querying the distinct [Month] & Year values from the query
Declare @PivotValues as VARCHAR(8000)
Declare @PivotSum as VARCHAR(8000)
Declare @PivotTotal as VARCHAR(8000)
Declare @PivotTypes as VARCHAR(8000)
Declare @MonthYear as VARCHAR(25)
Declare @EDATEMM as INT
Declare @Year as INT

--Loop over the @YearMonth CURSOR table to build our PivotField Values
DECLARE YearMonth_cursor CURSOR FOR
SELECT DISTINCT dbo.MonthMst.[Month] + '-' + CAST(Year([EDATE]) as varchar(4)), EDATEMM, Year([EDATE])
FROM dbo.MonthMst INNER JOIN
@MasterSubset AS MS ON dbo.MonthMst.cLngMonthCode = MS.EDATEMM
Order By Year([EDATE]), EDATEMM

OPEN YearMonth_cursor

FETCH NEXT FROM YearMonth_cursor
INTO @MonthYear, @EDATEMM, @Year

WHILE @@FETCH_STATUS = 0
BEGIN
--Build the PivotValues to be used in forming the Pivot Statement
If COALESCE(@PivotValues, '') = ''
SET @PivotValues = '[' + @MonthYear + ']'
ELSE
SET @PivotValues = @PivotValues + ',[' + @MonthYear + ']'

--Build the PivotTotal for use in forming the Grand Total Column
If COALESCE(@PivotTotal, '') = ''
SET @PivotTotal = 'COALESCE([' + @MonthYear + '],0)'
ELSE
SET @PivotTotal = @PivotTotal + ' + COALESCE([' + @MonthYear + '],0)'

--Build the PivotSum for use in forming the Grand Total Row
If COALESCE(@PivotSum, '') = ''
SET @PivotSum = 'SUM(COALESCE([' + @MonthYear + '],0))'
ELSE
SET @PivotSum = @PivotSum + ', SUM(COALESCE([' + @MonthYear + '],0))'

--Build the PivotTypes for use in declaring a Table Variable
If COALESCE(@PivotTypes, '') = ''
SET @PivotTypes = '[' + @MonthYear + '] INT NULL'
ELSE
SET @PivotTypes = @PivotTypes + ', [' + @MonthYear + '] INT NULL'

-- Get the next YearMonth.
FETCH NEXT FROM YearMonth_cursor
INTO @MonthYear, @EDATEMM, @Year

END

--Add the Grand Total Column to the PivotValues
--This keeps us from having to modify the Structure in order to add
--The Grand Total column
set @PivotValues = @PivotValues + ',[Grand Total]'

--We will need to SUM the Grand Total Column in order to build the last value Grand Total
--Row
set @PivotSum = @PivotSum + ', SUM([Grand Total])'

--Add the Grand Total column to our PivotTypes as this needs to be a column in our Table Variable
set @PivotTypes = @PivotTypes + ',[Grand Total] INT NULL'

CLOSE YearMonth_cursor
DEALLOCATE YearMonth_cursor

DECLARE @SQL as NVARCHAR(4000)

--Stuff our Data into a Temporary table
--to be used in the dynamic SQL below
Select [Month] + '-' + CAST([Year] as varchar(4)) AS MonthYear, GroupCol1, GroupCol2, GroupCol3, AggregatCol
INTO #PivotData
from
(Select Month, Year, GroupCol1, GroupCol2, GroupCol3, AggregatCol From DataTable) as DT

--Now here was the tricky part. Since I didn't know what my pivot columns would be I needed
--to build dynamic SQL however I couldn't figure out how to get the data to return properly. So
--I basically created a dynamic stored proc without actually creating a stored proc.
SET @SQL = 'DECLARE @PivotTable1 TABLE( GroupCol1 varchar(50) NOT NULL,
GroupCol2 varchar(20) NOT NULL,
GroupCol3 varchar(20) NOT NULL,
' + @PivotTypes + ');
INSERT INTO @PivotTable1 (GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + ' )
Select GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + '
FROM
(Select GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + '
FROM
(
Select MonthYear,GroupCol1,GroupCol2,
GroupCol3, AggregateCol
from #PivotData
) As Source
PIVOT
(
SUM(Source.AggregateCol)
FOR MONTHYEAR IN (' + @PivotValues + ')
) As PivotTable) As PT;
Update @PivotTable1 Set [Grand Total] = ' + @PivotTotal +
'; Insert Into @PivotTable1 (GroupCol1, GroupCol2, GroupCol3, ' + @PivotValues + ')
Select ''Grand Total'', '''', '''', ' + @PivotSum + ' From @PivotTable1;
Select * From @PivotTable1;'

Exec sp_executesql @sql

Drop Table #PivotData

And here are the results:
GroupCol1 GroupCol2 GroupCol3 January-2007 February-2007 Grand Total
-------------------------------------------------------------------------------------------------
GroupValue1 Group2Value1 Group3Value1 391 356 747
GroupValue2 Group2Value2 Group3Value1 356 419 775
GroupValue3 Group2Value3 Group3Value1 181 181 362
Grand Total 928 956 1884


Post the table CREATE statements and some sample data according to the URL in my signature... you'll be surprised at how many will give you a working code example in return... Wink

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116619 Visits: 41430
Matt Marston (5/13/2008)
The PIVOT statement can be useful at times, but it is handy to know that anything that can be done with a PIVOT statement can be converted to an equivalent query without the PIVOT statement.


For those that don't know about Matt's fine example, it's called a "Cross Tab" query and examples can be found in Books Online.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116619 Visits: 41430
Good documentation and examples of the problem, Ryan!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search