• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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.
  • 3
  • 2
1 Solution
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.
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.
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
AlHal2Author Commented:
Good idea.  I'll give it a try.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now