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
Who is Participating?
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!
Patrick MatthewsCommented:
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?
sqlcuriousAuthor Commented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
sqlcurious, do you need more help on this question?
sqlcuriousAuthor Commented:
excellent! thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.