Pivot Help

• mashispano

  SSC Enthusiast

  Points: 167

  More actions

  November 17, 2024 at 6:17 pm

  #4485030

  Hi Guys

  I need help pivoting the following

  SELECT [ID],[code_name],,[retail_price] FROM products

  1 | Rice & Beans | Small | 10.00

  2 | Rice & Beans | Medium | 15.00

  3 | Rice & Beans | Large | 20.00

  TO be Pivot as follow

  SELECT [DESC],[Small],[Medium],[Large] FROM Pivot_Table

  Rice & Beans | 10.00 | 15.00 | 20.00

  This is my code but it is not working as expected, I am getting NULL value for the description

  SELECT [Desc], ISNULL([small],0) AS Small,ISNULL([medium],0) AS Medium,ISNULL([large],0) AS Large
  FROM
  (
  SELECT [code_name],,[retail_price] FROM products
  ) p
  PIVOT
  (
      SUM([retail_price])
      FOR [Size] IN ([Desc],[small],[medium],[large])
  ) AS pvt

  • This topic was modified 3 hours, 55 minutes ago by  mashispano.

• Jeff Moden

  SSC Guru

  Points: 1004311

  More actions

  November 17, 2024 at 8:04 pm

  #4485065

  Howdy and welcome aboard!

  Please see the article at the first link in my signature line below for how to post "Readily Consumable Data" to help us give you the best help possible as soon as possible.   The article contains one method using "UNION ALL" and the following example uses "VALUES".

  --===== Create the readily consumable data,
       -- which explains everything we need to
       -- know about the data, as well.
     DROP TABLE IF EXISTS #Products;
  GO
   CREATE TABLE #Products
          (
           ID           int
          ,[DESC]       varchar(20)
          ,code_name    varchar(20)
          ,retail_price decimal(9,2)
          )
  ;
   INSERT INTO #Products WITH (TABLOCK) --Habit for "Minimally Logged" performance
   VALUES  (1,'Rice & Beans','Small' ,10.00)
          ,(2,'Rice & Beans','Medium',15.00)
          ,(3,'Rice & Beans','Large' ,20.00)
  ;
  GO

  Then we can say "Sure... no problem.  Here's the code and the results".  You should always test your code to make sure that it works 🙂

  --===== This is known as a CROSSTAB. It's faster and more 
       -- flexible than PIVOT. See the following article, as well.
       -- https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-converting-rows-to-columns-1
   SELECT [DESC]
          ,Small  = SUM(IIF(code_name = 'Small' ,retail_price,NULL))
          ,Medium = SUM(IIF(code_name = 'Medium',retail_price,NULL))
          ,Large  = SUM(IIF(code_name = 'Large' ,retail_price,NULL))
     FROM #Products
    GROUP BY [DESC]
    ORDER BY [DESC]
  ;

  Results:

  

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