query for dynamically generated columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query for dynamically generated columns

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-04-04 : 17:54:01

I have a table with dynamically generated column headers based on the current date:

CustomerID, Apr_2011, Mar_2011, Feb_2011, Jan_2011, Dec_2010, Nov_2010

I am joining this table to my customer table but I'm not quite sure how to select the dynamically generated column names.


SELECT
 c.CustomerName
,c.CustomerCity
,t.Month_Year1 --> Apr_2011
,t.Month_Year2 --> Mar_2011
, etc.
FROM Customers c
INNER JOIN SalesTable t ON c.CustomerID = t.CustomerID

Since the month columns are always changing, how can I find out what column names to put into my query. Thanks for any help you can provide.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-04 : 18:20:21

You will have to use dynamic SQL for this.

Why do you have a design like this? Can you normalize it instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-04-05 : 15:03:31

My sales table is automatically generated over again each month using the PIVOT function. It only contains 6 months worth of Sales so therefore the column headings change. The table is used in a report that's generated by an stored procedure.
Not sure how to further normalize this.
To use dynamic sql as you suggest should I use the information_schema.columns parameter and store the results in a variable?


DECLARE @sql nvarchar(MAX)
SELECT @sql= COALESCE(@sql + ' ,', '') + 
+ quotename(column_name)FROM information_schema.columns WHERE table_name = 'SalesTable'

Then how would I construct the query ?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-05 : 15:06:48

Tara is right! how do the table headings change? do they say something like 6month_sales, 6month_part_id? you might not need to do dynamic query if you have good design upfront. For example you could add a month, date, year columns or just a sales_date column and you might not need to do any dynamic querying

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources