Pivot query

Question

i have a simple sql query as below that produces a row of headings (in currencies) and one row below of totals for each currency.  This is a query on one talble.  I will be very grateful for the relevant code to transpose this data so the currencies are in one column and the amounts in the next coluln.  I am relatively new to SQL and have tried ever thing i can think of having looked at various Pivot examples on the web.

This is being run on SQL Server 2008.

<!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman","serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

SELECT    

             SUM ( CASE DebtIssuance. CurrencyID WHEN 41 THEN

                    DebtIssuance. nominal ELSE 0 END ) GDP,

            SUM ( CASE DebtIssuance. CurrencyID WHEN 40 THEN

                    DebtIssuance. nominal ELSE 0 END ) EUR,

            SUM ( CASE DebtIssuance. CurrencyID WHEN 43 THEN

                    DebtIssuance. nominal ELSE 0 END ) USD,

            SUM ( CASE DebtIssuance. CurrencyID WHEN 37 THEN

                    DebtIssuance. nominal ELSE 0 END ) AUD,

            SUM ( CASE DebtIssuance. CurrencyID WHEN 39 THEN

                    DebtIssuance. nominal ELSE 0 END ) CHF,

            SUM ( CASE DebtIssuance. CurrencyID WHEN 42 THEN    

                    DebtIssuance. nominal ELSE 0 END ) JPY,

            SUM ( CASE DebtIssuance. CurrencyID WHEN 38 THEN

                    DebtIssuance. nominal ELSE 0 END ) CAD

           

            FROM

                  DebtIssuance

           WHERE

                 DebtIssuance. MarkedForDeletion = 0 AND DebtIssuance. Validated = 1 AND DebtIssuance. StatusCodeID = @STATUSCODE_Certified

Answer 1

I think you're looking for UNPIVOT. Your current code is pivoting the data. If you need to keep them as Currency /Value, then

select CurrencyID, sum(Nominal) as Nominal from

DebtIssuance

           WHERE

                 MarkedForDeletion = 0 AND Validated = 1 AND StatusCodeID = @STATUSCODE_Certified

group by CurrencyID

 

 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 2

Thank you - i am relatively new to all this so will try that in the morning as do not have SQL Server on my home PC.

Answer 3

The code I showed will show Currency as a number (CurrencyID). Do you have a Currency table on your system (CurrencyID, CurrencyAbbreviation, other info)? If yes, we may want to join with this table to show the name of the currency rather than code.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

yes there is a currency table that allocates a currency to each number so i guess you are right it needs a join.  Will have a look at it tomorrow and have a play with it.

Many thanks

Answer 5

That works perfectly with a Join.

Many thanks for that very much appreciated