none
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
    Wednesday, March 17, 2010 9:41 PM

Answers

  • 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
    Wednesday, March 17, 2010 9:44 PM
    Moderator

All replies

  • 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
    Wednesday, March 17, 2010 9:44 PM
    Moderator
  • 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.
    Wednesday, March 17, 2010 9:49 PM
  • 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
    Wednesday, March 17, 2010 10:04 PM
    Moderator
  • 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
    Wednesday, March 17, 2010 10:38 PM
  • That works perfectly with a Join.

    Many thanks for that very much appreciated
    Thursday, March 18, 2010 8:25 AM