[Last Call] Learn how to a build a cloud-first strategyRegister Now


Dynamic sql and Pivoting

Posted on 2007-08-09
Medium Priority
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
  • 3
  • 2

Author Comment

ID: 19660885
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.

Expert Comment

ID: 19667774
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

ID: 19669376
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.

Accepted Solution

Minna earned 375 total points
ID: 19690040
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

ID: 19690324
Good idea.  I'll give it a try.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 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