change table result RSS

• • Reply
  zhyanadil.it...

  Member

  75 Points

  513 Posts

  change table result

  Nov 20, 2019 07:56 AM|zhyanadil.it@gmail.com|LINK

  i have this result

  stock_qty3                               amount3                item_code3                                                bom                       

  6                                                    5                     بسكويت السكر "نكهة الزبدة" 4 كغم                bom1

  3                                                    3                   بسكويت السكر "نكهة الزبدة" 4 كغم                 bom2

  i want convert to this result

  item                                                                   field                                 BOM1                     BOM2

  بسكويت السكر "نكهة الزبدة" 4 كغم                      stock_qty3                         6                            3

                                                                             amount3                            3                           3

• • Reply
  Yongqing Yu

  Contributor

  3710 Points

  1043 Posts

  Re: change table result

  Nov 21, 2019 05:49 AM|Yongqing Yu|LINK

  Hi zhyanadil,

  For this issue, I suggest that you can use pivot and unpivot methods to implement it.

  In the following statements, the #Table is the data source, you can get the result you need by executing the following SQL statement:

  select * from (
     select item_code3 AS item,field,bom,bomvalue from #Table
       UNPIVOT(   
       bomvalue FOR field IN (stock_qty3, amount3)) u 
  ) c
  PIVOT(max(bomvalue) for bom in (BOM1,BOM2)) a

  Best Regards,

  YongQing.

  MSDN Community Support
  
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.