Simple Pivot

• SQLCurious

  SSCrazy

  Points: 2339

  More actions

  July 10, 2014 at 4:11 pm

  #293274

  I have a master (lookup) table that holds details of our product codes. I have another table that keeps track of the quotes for each of these codes. For a given code, there can be several quotes and each of them will have a created date. I am looking to get the most recent quote for each code. Additionally, I want to pivot this result so that my output is a single row with each column representing a code.

  Following is the SQL script to create sample data...

  DECLARE @CodeTypes TABLE (CodeID INT, CodeName VARCHAR (20))

  INSERT @CodeTypes (CodeID, CodeName) Values (1001, 'Promotion'), (1002, 'HomeLoan'), (1003, 'AutoLoan'), (1004, 'CashAdvance'), (1005, 'CreditRefi')

  DECLARE @QuoteData TABLE (CodeID INT, Quote DECIMAL (10, 2), CreatedDate DateTime)

  INSERT @QuoteData VALUES (1001, 1.23, '4/1/2014'), (1001, 1.22, '4/10/2014'), (1001, 1.25, '5/1/2014'), (1001, 1.22, '5/10/2014')

  INSERT @QuoteData VALUES (1002, 2.34, '4/11/2014'), (1002, 2.32, '5/14/2014'), (1002, 2.25, '5/31/2014')

  INSERT @QuoteData VALUES (1003, 3.45, '3/31/2014')

  INSERT @QuoteData VALUES (1004, 4.56, '6/18/2014')

  INSERT @QuoteData VALUES (1005, 5.67, '5/12/2014'), (1005, 5.65, '5/22/2014'), (1005, 5.60, '6/21/2014')

  select * from @QuoteData

  select * from @CodeTypes

  Following images show the data in my source tables along with a sample of the output I am looking to get...

  

  - SC

• brad.mason5

  Hall of Fame

  Points: 3299

  More actions

  July 10, 2014 at 5:59 pm

  #1728566

  This is using the PIVOT operator. Case statements can also work and sometimes perform better.

  SELECT *

  FROM (SELECT RowNum = Row_number()

  OVER(

  PARTITION BY codes.CodeName

  ORDER BY CreatedDate DESC)

  ,codes.CodeName

  ,data.Quote

  FROM @QuoteData data

  JOIN @CodeTypes codes

  ON data.CodeID = codes.CodeID) AS quotes

  PIVOT ( Max(Quote)

  FOR CodeName IN ([AutoLoan],

  [CashAdvance],

  [CreditRefi],

  [HomeLoan],

  [Promotion])

  )AS pvt

  WHERE pvt.RowNum = 1