Simple Pivot

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply