我可以转动这张桌子吗？ [英] Can I Pivot this table

问题描述

<b></b><b></b><b><b></b></b>I have this table

<pre>
69  15984   3rd Party   WODI, HILDA AHUROLE (MS)    GLOBAL BEST PRACTICES IN MANAGERIAL EXCELLENCE  2014-06-30 00:00:00.000 12  Estacode                USD     470 40  USD
69  15984   3rd Party   WODI, HILDA AHUROLE (MS)    GLOBAL BEST PRACTICES IN MANAGERIAL EXCELLENCE  2014-06-30 00:00:00.000 12  CourseFee               EUR     99  10  EUR
69  15984   3rd Party   WODI, HILDA AHUROLE (MS)    GLOBAL BEST PRACTICES IN MANAGERIAL EXCELLENCE  2014-06-30 00:00:00.000 12  AirTicket               USD     25  40  USD
19  16318   Staff   JONAH STEPHEN KEHINDE   BUILDING PERSONAL LEADERSHIP SKILLS + IMPROVING OPERATION PERFORMANCE & PRODUCTIVITY    2014-09-07 00:00:00.000 12  Estacode                USD     375 40  USD
19  16318   Staff   JONAH STEPHEN KEHINDE   BUILDING PERSONAL LEADERSHIP SKILLS + IMPROVING OPERATION PERFORMANCE & PRODUCTIVITY    2014-09-07 00:00:00.000 12  CourseFee               NGN     120000  20  NGN
19  16318   Staff   JONAH STEPHEN KEHINDE   BUILDING PERSONAL LEADERSHIP SKILLS + IMPROVING OPERATION PERFORMANCE & PRODUCTIVITY    2014-09-07 00:00:00.000 12  AirTicket               NGN     55000   20  NGN
51  16317   3rd Party   KOLAPO, JOLAYEMI SIKIRAT (MRS.) LEADERSHIP & MGT. MASTER    2014-10-20 00:00:00.000 12  AirTicket               USD     235 40  USD
51  16317   3rd Party   KOLAPO, JOLAYEMI SIKIRAT (MRS.) LEADERSHIP & MGT. MASTER    2014-10-20 00:00:00.000 12  CourseFee               NGN     56000   20  NGN
11  17010   3rd Party   OTARO STEPHEN   MINI-MBA IN STRATEGIC MANAGEMENT AND LEADERSHIP 2014-07-14 00:00:00.000 12  Estacode                USD     440 40  USD
11  17010   3rd Party   OTARO STEPHEN   MINI-MBA IN STRATEGIC MANAGEMENT AND LEADERSHIP 2014-07-14 00:00:00.000 12  CourseFee               USD     105 40  USD
11  17010   3rd Party   OTARO STEPHEN   MINI-MBA IN STRATEGIC MANAGEMENT AND LEADERSHIP 2014-07-14 00:00:00.000 12  AirTicket               NGN     250000  20  NGN

</pre>

Column headings as follows

<pre>
trainingID  staffid  stafftype  sname  coursetitle  startdate  duration coursename currency amount curid
</pre>

We can ignore the last colunm.

Can I pivot this table to form a report like this:
<pre>

trainingid sname courstitle estacode amount Currency Airticket Amount Currency CourseFee amount Currency
</pre>

Where amount is the sum of the particular currency based on the trainingid and staffid. Sample below for the first person.  Notice that the first 3 rows belong to one person.

<pre>
69   WODI, HILDA AHUROLE (MS)  GLOBAL BEST PRACTICES IN MANAGERIAL EXCELLENCE Estacode 40 USD Coursefee 99 EUR Airticket 25 USD
</pre>

Thanks in anticipation

推荐答案

我想你需要很少的行数据，比如esta代码作为你的列名吗？

I guess you need few of your row data like esta code to be your column name?

SELECT *
FROM <yourtable>
PIVOT(SUM(currency)
      FOR Course IN (<rowdata1>, <rowdata2>)) AS PVTTable

where rowdata1 and rowdata2 are the data that you want as columns</rowdata2></rowdata1></yourtable>

希望这有助于

Hope this helps

这篇关于我可以转动这张桌子吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！