Pivot to get all rows

• tshad

  SSCertifiable

  Points: 5905

  More actions

  April 25, 2018 at 11:26 pm

  #344504

  I am trying to pivot and get both a summary and detail result set.  The detail would get all rows with no summing.  I found that by uniquifying the records, I can do this but then it doesn't sum.

  If I start with this table that has no "Type" column, the sum works fine but not the detail.
  
DECLARE @BookSales TABLE
(BookType VARCHAR(20), SalesYear INT, BookSales MONEY)INSERT INTO @BookSales VALUES('Fiction', 2014, 11201)
INSERT INTO @BookSales VALUES('Fiction', 2014, 12939)
INSERT INTO @BookSales VALUES('Fiction', 2013, 10436)
INSERT INTO @BookSales VALUES('Fiction', 2013, 9346)
INSERT INTO @BookSales VALUES('Nonfiction', 2014, 7214)
INSERT INTO @BookSales VALUES('Nonfiction', 2014, 5800)
INSERT INTO @BookSales VALUES('Nonfiction', 2013, 8922)
INSERT INTO @BookSales VALUES('Nonfiction', 2013, 7462)

  SELECT * 
  FROM @BookSales
    PIVOT(SUM(BookSales)
    FOR SalesYear IN([2013], [2014])
    ) AS PivotSales

  
SELECT * 
FROM @BookSales
  PIVOT(SUM(BookSales)
  FOR SalesYear IN([2013], [2014])
  ) AS PivotSales


  

  This works fine for me for a summary result.

  But I also need need a detail set of rows.  I understand why they need to be summed, because there would be no way to know which 2013 amount goes with which 2014 amounts.  So I added another column (Type) and used this to tie the years and sums together.

  
DECLARE @BookSales TABLE
(BookType VARCHAR(20), Type INT, SalesYear INT, BookSales MONEY)INSERT INTO @BookSales VALUES('Fiction', 1, 2014, 11201)
INSERT INTO @BookSales VALUES('Fiction', 2, 2014, 12939)
INSERT INTO @BookSales VALUES('Fiction', 1, 2013, 10436)
INSERT INTO @BookSales VALUES('Fiction', 2, 2013, 9346)
INSERT INTO @BookSales VALUES('Nonfiction', 1, 2014, 7214)
INSERT INTO @BookSales VALUES('Nonfiction', 2, 2014, 5800)
INSERT INTO @BookSales VALUES('Nonfiction', 1, 2013, 8922)
INSERT INTO @BookSales VALUES('Nonfiction', 2 ,2013, 7462


  Now if I do this query I get detail rows:
  
SELECT PivotSales.BookType,
       PivotSales.Type,
       PivotSales.[2014],
       PivotSales.[2013]
FROM @BookSales
  PIVOT(SUM(BookSales)
  FOR SalesYear IN([2013], [2014])
  ) AS PivotSales


  Gets me this result:
  

  I would now assume I can get the same result as the first query from this new table by taking out the "PivotSales.Type" from the select list so that the sum will sum based on only the BookType column.
  SELECT PivotSales.BookType,
       PivotSales.[2014],
       PivotSales.[2013]
FROM @BookSales
  PIVOT(SUM(BookSales)
  FOR SalesYear IN([2013], [2014])
  ) AS PivotSales 

  But that is not the case.  I get the same result as the detail so that the sum is based on the BookType and the "Type" columns.

  What am I missing here?

  Thanks,

  Tom

• george_at_sql

  SSCommitted

  Points: 1967

  More actions

  April 25, 2018 at 11:58 pm

  #1988297

  i think this is has to do with what we use in the FROM portion of the TABLE being pivoted -->"@BookSales" . In this case its BookType,Type,BookSales,SalesYear
  
SELECT PivotSales.BookType,
   PivotSales.[2014],
   PivotSales.[2013]
FROM @BookSales /*Here we say to use all legitimate columns of @Booksales that work for PIVOT*/
PIVOT(SUM(BookSales)
FOR SalesYear IN([2013], [2014])
) AS PivotSales


  If instead this was changed to 

  
SELECT *
from ( select BookType, BookSales, SalesYear /*Note i  have not included the column Type here.*/
             from @BookSales 
   ) as x
PIVOT(SUM(x.BookSales)
FOR x.SalesYear IN([2013], [2014])
) AS PivotSales2;


  I think you should be getting the output identical to that of the very first query

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  April 26, 2018 at 10:00 am

  #1988379

  I've always find simpler to use cross-tabs instead of the pivot operator. It's more flexible and it can become simpler and faster.
  
SELECT BookType,
   SUM( CASE WHEN SalesYear = 2014 THEN BookSales END) AS [2014],
   SUM( CASE WHEN SalesYear = 2013 THEN BookSales END) AS [2013]
FROM @BookSales
GROUP BY BookType


  For more information, check the following article:
  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• tshad

  SSCertifiable

  Points: 5905

  More actions

  April 26, 2018 at 2:59 pm

  #1988403

  Both ways do the job.
  Thanks.