Dynamic Table Header Name and count

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

General SQL Server Forums

New to SQL Server Programming

Dynamic Table Header Name and count

Author

Topic

anujpratap84
Starting Member

45 Posts

Posted - 2012-01-16 : 01:28:30

Hi All,

I want to return a result set containing 4 static column and after that collumns name with name like 1 Jan 2012 to number of days in month.
ex.
A B C D 1 Jan 2012 1 Jan 2012 2 Jan 2012 .... 31 Jan 2012

Number of Columns vary with number of days in month.

Please suggest a good solution for this query.
I can also Hide colum at my front end if query will return 35 rows(4+max number of days in month) every time and number of days is less than 31 in that month.
Please help.
Thanks in advance..

Anuj Pratap Singh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-16 : 01:33:13

First, you will need to use Dynamic SQL http://www.sommarskog.se/dynamic_sql.html

Second, pivoting. http://msdn.microsoft.com/en-us/library/ms177410.aspx

Dynamic Pivot / CrossTab query
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 02:00:28

you might need a calendar table so that you've all dates you need to pivot

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-16 : 03:24:55

quote:
Originally posted by visakh16

you might need a calendar table so that you've all dates you need to pivot

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes. absolutely !

KH
[spoiler]Time is always against us[/spoiler]

anujpratap84
Starting Member

45 Posts

Posted - 2012-01-16 : 06:34:57

Thanks 4 reply..
but how i use this in my query to show result under the date header..
Can you please provide a sample example..
Like if i have 2 tables where all required data stored i'll apply join between those two tables and get data but how i use this funtion in that query..
Thanks for your support...

quote:
Originally posted by visakh16

you might need a calendar table so that you've all dates you need to pivot

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Anuj Pratap Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 06:48:40

you will use like below


SELECT f.[Date],
... other fields
FROM dbo.CalendarTable(@yourstartdate,@yourenddate,0,0)f
LEFT JOIN yourtable t
ON t.datefield = f.[Date]
...

@yourstartdate and @yourenddate are varibles through which you pass start and end values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources