Pivot dynamic column names

• tshad

  SSCertifiable

  Points: 5905

  More actions

  July 5, 2018 at 5:03 pm

  #365915

  I was wondering if there was a way to do this without dynamic sql?

  
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)

  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

  The result would be:
  

  

  What I would like to do is change the code where the 2014 and 2013 are and change them to something like @Year1 and @Year2 which I would get from the table and use them in the query as bot the filter in the When clause and the alias.

  In my actual code this will be two dates in a month that would run each month with different dates.  It would run each month but always with two dates.

  Thanks,

  Tom

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  July 5, 2018 at 9:36 pm

  #1996476

  tshad - Thursday, July 5, 2018 5:03 PM

  I was wondering if there was a way to do this without dynamic sql?

  
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)

  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

  The result would be:
  

  

  What I would like to do is change the code where the 2014 and 2013 are and change them to something like @Year1 and @Year2 which I would get from the table and use them in the query as bot the filter in the When clause and the alias.

  In my actual code this will be two dates in a month that would run each month with different dates.  It would run each month but always with two dates.

  Thanks,

  Tom

  Since you already know how to do CROSSTABs manually, you've got about 80% of the problem licked.  See the following article for the next step. 

  http://www.sqlservercentral.com/articles/Crosstab/65048/

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• SatishChandra_SQL

  Old Hand

  Points: 316

  More actions

  July 12, 2018 at 7:48 am

  #1997295

  Even I was searching for something similar, and found the below. This works perfectly fine with "Pivoting dynamic number of rows TO columns"
  Let me know if this works or you are looking for something different.
  

  CREATE TABLE #BookSales
  (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)

  INSERT INTO #BookSales VALUES('Nonfiction', 2 ,2015, 1000)

  INSERT INTO #BookSales VALUES('Nonfiction', 2 ,2015, 1000)

  SELECT * FROM #BookSales
  

  DECLARE @col VARCHAR(1000)
  

  DECLARE @sql VARCHAR(2000)
  

  SELECT @col = COALESCE(@col + ', ','') + QUOTENAME(SalesYear) from #BookSales Group by SalesYear
  

  Set @sql='select * from (select BookType, BookSales, SalesYear from #BookSales ) src PIVOT (sum(BookSales) FOR SalesYear IN ('+@col+')) pvt'
  

  EXEC(@sql)
  

  print(@sql)

  Thanks,
  Satish Chandra