Dynamic Column Headers - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Dynamic Column Headers

Author

Topic

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-03-14 : 10:26:41

Gool Morning all.

Is there way to create a view that has dynamic column headings?

I would like to create a view that shows products by week. and I would like the column names to show the week they are representing?

any ideas? PS, this is not a crosstab report but rather a dynamic way of displaying column headers.

thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-14 : 10:50:34

This is a reporting issue; do it a the client.

If you a returning data starting from 1/1/2000 and each column represents a week, and the column names are "1","2", etc, in your report design use a formula to display the column headers you need.

For example, in Crystal, create formulas called Col1, Col2, etc, and for each one make it equal to:

StartDate + n * 7

where n is the column number. Then, display this formula in place of each column, formatted as you like.

Don't go through hoops changing column names and writing dynamic SQL just so that the query analyzer displays nice headings for each column.

- Jeff

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-03-14 : 11:16:52

Actually, I have a defualt asp page I'm using to display all my sql views and sprocs so I don't want to add to my responsibility by managing both the views AND the client side design.

If I can dynamically create the column names in sql, it would save me from having to do anything on the reporting end.

Any advice on how to do this using sql?

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-14 : 14:43:52

Why don't you post a sample of what the results would look like. That might help.

Brett

8-)

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-03-14 : 14:52:00

item wk ending 01/01/03 wk ending 01/08/03
abc 125 357
def 765 457

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-14 : 15:07:39

Well a couple of things, 01/01/03 is not the end of a week. Also You're stringing out the columns and basically "flipping" the data on it's side. How many weeks do you want to display. To get the data in the manner you want, you'll have to join that table to itself for every weekly grouping. Do you have the query written yet? If so, it might be a simple matter to add headings to the data.

Brett

8-)

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-03-14 : 15:09:36

i was justing posting that as an example, but yes the query is written.

How can you dynamically select the column name?

thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-14 : 15:36:29

Ok, if you've written it, try:

SELECT 'ITEM','WEEK ENDING' + Convert(varchar(25),@yourDate),'WEEK ENDING' + Convert(varchar(25),DATEADD(Week,1,@yourDate)),'WEEK ENDING' + Convert(varchar(25),DATEADD(Week,1,@yourDate)),ect
UNION ALL
SELECT col1, col2, ect FROM (Your Query)

Whatdya think?

Brett

8-)

mdelgado
Posting Yak Master

141 Posts

Posted - 2003-03-14 : 17:18:22

Doesn't work.
There has to be a way to do this...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-14 : 18:31:08

I still feel the client should do this stuff ...

... however:

using dynamic sql:

exec('

SELECT item,
sum(case when week=1 then data else 0 end) as [Week Ending ' + convert(varchar(10),DateAdd(w,1,@startDate)) + '],
sum(case when week=2 then data else 0 end) as [Week Ending ' + convert(varchar(10),DateAdd(w,2,@startDate)) + ']
FROM
YourSQL
GROUP BY Item')

Add more columns as needed. You must have a starting date (@startdate) and YourSQL must return a field called "Week" which is the week# or the column number you wish to the SUM() the field which I called "data" into.

Hope that helps.

- Jeff

Edited by - jsmith8858 on 03/14/2003 18:32:10

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-14 : 21:12:51

Why doesn't work? Are your data columns of differnt data types? Not sure. Post your query and I'll make it work.

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-14 : 21:55:22