We help IT Professionals succeed at work.

pivot table query

Hi experts, attached is how I want the original table data to be transformed to the pivot table, pls suggest the query for this,. thanks
Watch Question

Top Expert 2010

Some questions:
1) Is RowNum an actual column in the table?
2) If so, how is it generated?  For example, is it a sequence number for the tale as a whole, or does it restart at 1 for each customer?
3) Did you mean to handle the Value, ImagePath, and Description columns the way you did in the example?
4) What would you expect to see if a given customer had, say, 47 different items in the original table?


Hi Patrick, Sorry about not giving further details in the original post.
RowNum is actual column, it does start with 1, this is generated by a different process and every customers will have only 3 items, so there wont be a question of 47 different items
About your pointer 3, I am sorry about the data , it got skewed while copying, my intention was to remove value field completely since I don't need it in the new table, attached is a much relevant one.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
A query like this should do the trick:
      1 as RowValue	
    , Customer_No
    , MAX(EmailAddress) as EmailAddress
    , max(case when RowNum = 1 then Inventory_No end) as Inventory_No1	
    , max(case when RowNum = 1 then Sale_No      end) as Sale_No1	
    , max(case when RowNum = 1 then Lot_No       end) as Lot_No1	
    , max(case when RowNum = 1 then Venue        end) as Venue1	
    , max(case when RowNum = 1 then PCGS_No      end) as PCGS_No1	
    , max(case when RowNum = 1 then Grade        end) as Grade1	
    , max(case when RowNum = 1 then Service      end) as Service1	
    , max(case when RowNum = 1 then ImagePath    end) as ImagePath1	
    , max(case when RowNum = 1 then Description  end) as Description1	

    ... repeat similar block for rownum 2 and 3

from your_table

Open in new window

HOWEVER you do NOT want to, or need to, repeat Customer_No 3 times!
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

sqlcurious, do you need more help on this question?


excellent! thanks