Pivoting Multiple Columns

Question

Is there a way where I can pivot multiple columns from my table to pivot into 3 currencies (LAK, THB, USD). The columns I want to pivot are premium, discountedPremium, registryFee, vat, etc. Please see image below.

I want the result to be like in the image below:

thank you for your guidance.

Jes Sie · Answer

Nope! I'm not giving up on this Nitin. For now I need an immediate course of action for me to continue my development rather than waiting a lot of time trying to figure out what to do. Sometimes you just need to maneuver a little bit then get back to that track and face the issue head-on. My company has given me a lot on my plate more than I could chew.

Nitin Sontakke · Answer

Humm.... that is gone bit too far I believe. May be you should have been bit more persistent and interactive on the question. The dialoge is the probably most important thing to resolve issues. Anyways! Or may be we fell short to help you.

Jes Sie · Answer

I didn't get what I wanted so I re-designed my table to achieve my purpose. Thanks so much Nitin for all the knowledge you've always shared.

Nitin Sontakke · Answer

It isn't quite clear if your problem is soved or not. Please do let us know. Be rest assured that we are here to help. As stated earlier please do not expect that everything can be achieved in just one select statement. Always break down the problem and deal with it in a smaller chunks. This is a technique once learned (and tell us if you know how to do it) can be applied everywhere. Hope it helps!

Jes Sie · Answer

Thank you Nitin for your suggestion. Greatly appreciated.

Nitin Sontakke · Answer

Your another question ' Error Executing Stored Procedure with Dynamic Pivot ' is locked and cannot be replied to. On that question your query on line 34 is probably returning duplicate pageId which is causing an issue. Consider applying DISTINCT to that query. Hope it helps!

Nitin Sontakke · Answer

In addition I would also like to add that do not expect and entire result from sql server. What I mean to say is from sql server you might get columns like pre_lak pre_thb pre_usd dis_lak and so on. This is easier to achieve in a query using pivot. Then let any UI / front-end / reporting tool may take the responsibility of grouping columns as shown in image posted by you. BTW the currency values must be enclosed in quotation marks I believe. Hope it helps.

Vishal Joshi · Answer

As per your expected you can use grouping. and write stored procedure in which write multiple query to achive result. sample group by query select Premium count(*) from View_hpa_sales_report_V2 group by Premium

Tuhin Paul · Answer

WITH PivotedData AS ( SELECT id CASE WHEN premium_currency = 'LAK' THEN premium ELSE NULL END AS premium_LAK CASE WHEN premium_currency = 'THB' THEN premium ELSE NULL END AS premium_THB CASE WHEN premium_currency = 'USD' THEN premium ELSE NULL END AS premium_USD CASE WHEN discountedPremium_currency = 'LAK' THEN discountedPremium ELSE NULL END AS discountedPremium_LAK CASE WHEN discountedPremium_currency = 'THB' THEN discountedPremium ELSE NULL END AS discountedPremium_THB CASE WHEN discountedPremium_currency = 'USD' THEN discountedPremium ELSE NULL END AS discountedPremium_USD CASE WHEN registryFee_currency = 'LAK' THEN registryFee ELSE NULL END AS registryFee_LAK CASE WHEN registryFee_currency = 'THB' THEN registryFee ELSE NULL END AS registryFee_THB CASE WHEN registryFee_currency = 'USD' THEN registryFee ELSE NULL END AS registryFee_USD CASE WHEN vat_currency = 'LAK' THEN vat ELSE NULL END AS vat_LAK CASE WHEN vat_currency = 'THB' THEN vat ELSE NULL END AS vat_THB CASE WHEN vat_currency = 'USD' THEN vat ELSE NULL END AS vat_USD FROM transactions ) SELECT id MAX(premium_LAK) AS premium_LAK MAX(premium_THB) AS premium_THB MAX(premium_USD) AS premium_USD MAX(discountedPremium_LAK) AS discountedPremium_LAK MAX(discountedPremium_THB) AS discountedPremium_THB MAX(discountedPremium_USD) AS discountedPremium_USD MAX(registryFee_LAK) AS registryFee_LAK MAX(registryFee_THB) AS registryFee_THB MAX(registryFee_USD) AS registryFee_USD MAX(vat_LAK) AS vat_LAK MAX(vat_THB) AS vat_THB MAX(vat_USD) AS vat_USD FROM PivotedData GROUP BY id; A Common Table Expression (CTE) to pivot each field individually based on the currency and then aggregates the results using MAX to remove NULL values and get the desired format.