Dynamic sql and Pivoting

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.
AlHal2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlHal2Author Commented:
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.
MinnaCommented:
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.
AlHal2Author Commented:
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.
MinnaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlHal2Author Commented:
Good idea.  I'll give it a try.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.