Can I Pivot this table

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

XML

<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

Posted 2-Jul-15 5:37am

Member 2140788

Add a Solution

Comments

Arkadeep De 2-Jul-15 13:57pm

what is this...

Suvendu Shekhar Giri 2-Jul-15 16:21pm

Please format your question correctly and also provide the sample desired output.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

manognya kota · Accepted Answer · 2015-07-03T01:08:00

Solution 1

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

XML

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

Posted 3-Jul-15 1:08am

manognya kota

Comments

Member 2140788 8-Jul-15 7:17am

Thanx for the solution. worked great. But I still have more to ask. is it possible to have the another column pivot as well. Eg. I need the currency name ie. USD, NGN to pivot into columns. So that I could have something like this:

<name> <coursetitle> <curname1> <rowdata1> <curname2> <rowdata2> <curname3> <rowdata3>

Thanks in anticipation.

manognya kota 8-Jul-15 8:29am

Yes, you can add any number columns
SELECT *
FROM yourtable
PIVOT(SUM(currency)
FOR Course IN (rowdata1, rowdata2.... rowdataN)) AS PVTTable
Let me know if this helps and mark the question solved .
Happy coding !

Member 2140788 8-Jul-15 12:14pm

Thanks, for your prompt reply. I don't know why the sample table got pulled out from my comment above. i need to add the currency type i.e USD, NGN, EUR.

cur1 - rowdata1 - cur2 - rowdata2 - cur3 - rowdata3

The currency is part of the data in the rows.

Thank you in anticipation.

manognya kota 10-Jul-15 3:14am

Yes you can do it.