Dynamic sql and Pivoting

Posted on 2007-08-09
Last Modified: 2013-11-05
select distinct dschangedate
,(select count(dsexchangecode)
from pricehistory where dsexchangecode = 'LN' and
marketdate=p.dschangedate) as LN
,(select count(dsexchangecode)
from pricehistory where dsexchangecode = 'NY' and
marketdate=p.dschangedate) as NY
,(select count(dsexchangecode)
from pricehistory where dsexchangecode = 'FF' and
marketdate=p.dschangedate) as FF
from namechange as P where dschangedate >(select max(dschangedate)-8
from namechange)
Order by dschangedate

The query above returns these results

DsChangeDate    LN           NY                FF
20070802           3358      3302      8534
20070803           3361      3304      8529
20070806           3359      3301      8529
20070807           3359      3297      8537
20070808           3359      3296      8531
20070809           1      1      3

LN, NY and FF are 3 values out of a possible 215.  Can you give me some dynamic sql to retrieve all 215. Then I want to pivot the output, so the dates are column headings.
Question by:AlHal2

    Author Comment

    I should have mentioned that the 215exchange codess are in a separate table called "Exchange"
    There is a one to many relationship between the dsexchangecode in the exchange table and the dsexchangecode in the pricehistory table.

    Also I want the dates to be the last 7 days.
    LVL 3

    Expert Comment

    Why do it so hard?  You can create you data as such

    DsChangeDate    DSExchangeCode  Count
    20070802         LN              3358      
    20070802        NY              3302      
    20070802        FF       8534
    20070803        LN              3361      
    20070803        NY              3304      
    20070803        FF       8529
    20070806        LN              3359      
    20070806        NY              3301      
    20070806        FF              8529

    etc.  You can then use the date and the code as your dimension and the count as your data/measure.

    Author Comment

    Good idea.  I'm left with this.

    select dsexchangecode,marketdate,count(dscode) as PriceCount
    from pricehistory
    where marketdate > 1 week ago
    group by dsexchangecode,marketdate
    order by dsexchangecode,marketdate

    Now I want to make the marketdate into a column heading and the dsexchangecode as a row.
    LVL 3

    Accepted Solution

    What are you using to view all this data?  If you use the data as a fact table, you can build a cube and manipulate the data to the way you want it without having to rewrite the code.  Or If you don't want to build a cube, you can alternatively use something like Excel to create your pivot tables from the data and pivot the data as you see fit

    Author Comment

    Good idea.  I'll give it a try.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Title # Comments Views Activity
    Data lost in SQL server 23 83
    Left Justify field in Oracle 6 53
    SQL 2005 - Memory Table Column Names 11 54
    SQL syntax error in VBA 11 0
    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now