pivot help - 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

General SQL Server Forums

New to SQL Server Programming

pivot help

Author

Topic

kshitizgp
Starting Member

31 Posts

Posted - 2012-01-27 : 01:38:44

DECLARE @col VARCHAR(MAX)
DECLARE @sql nvarchar(2000)

select @col = COALESCE(@col + ', ','') + QUOTENAME(Work_Date)
from form_Details WHERE Work_Date BETWEEN '2012-01-01' and '2012-01-31' AND Employee_Id=338

WHEN I RUN THIS QUERY

[2012-01-01], [2012-01-02], [2012-01-03], [2012-01-04], [2012-01-05], [2012-01-06], [2012-01-07], [2012-01-08], [2012-01-09], [2012-01-10], [2012-01-11], [2012-01-12], [2012-01-13], [2012-01-14], [2012-01-15], [2012-01-16], [2012-01-17], [2012-01-18], [2012-01-19], [2012-01-20], [2012-01-21], [2012-01-22], [2012-01-23], [2012-01-24], [2012-01-25], [2012-01-26], [2012-01-27], [2012-01-28], [2012-01-29], [2012-01-30], [2012-01-31], [2012-01-01], [2012-01-02], [2012-01-03], [2012-01-04], [2012-01-05], [2012-01-06], [2012-01-07], [2012-01-08], [2012-01-09], [2012-01-10], [2012-01-11], [2012-01-12], [2012-01-13], [2012-01-14], [2012-01-15], [2012-01-16], [2012-01-17], [2012-01-18], [2012-01-19], [2012-01-20], [2012-01-21], [2012-01-22], [2012-01-23], [2012-01-24], [2012-01-25], [2012-01-26], [2012-01-27], [2012-01-28], [2012-01-29], [2012-01-30], [2012-01-31], [2012-01-01], [2012-01-02], [2012-01-03], [2012-01-04], [2012-01-05], [2012-01-06], [2012-01-07], [2012-01-08], [2012-01-09], [2012-01-10], [2012-01-11], [2012-01-12], [2012-01-13], [2012-01-14], [2012-01-15], [2012-01-16], [2012-01-17], [2012-01-18], [2012-01-19], [2012-01-20], [2012-01-21], [2012-01-22], [2012-01-23], [2012-01-24], [2012-01-25], [2012-01-26], [2012-01-27], [2012-01-28], [2012-01-29], [2012-01-30], [2012-01-31], [2012-01-01], [2012-01-02], [2012-01-03], [2012-01-04], [2012-01-05], [2012-01-06], [2012-01-07], [2012-01-08], [2012-01-09], [2012-01-10], [2012-01-11], [2012-01-12], [2012-01-13], [2012-01-14], [2012-01-15], [2012-01-16], [2012-01-17], [2012-01-18], [2012-01-19], [2012-01-20], [2012-01-21], [2012-01-22], [2012-01-23], [2012-01-24], [2012-01-25], [2012-01-26], [2012-01-27], [2012-01-28], [2012-01-29], [2012-01-30], [2012-01-31], [2012-01-01], [2012-01-02], [2012-01-03], [2012-01-04], [2012-01-05], [2012-01-06], [2012-01-07], [2012-01-08], [2012-01-09], [2012-01-10], [2012-01-11], [2012-01-12], [2012-01-13], [2012-01-14], [2012-01-15], [2012-01-16], [2012-01-17], [2012-01-18],

IT SHULD STOP AT 31 ....cause of this am getting error

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-27 : 02:55:31

how many records to get from the query below ?


select Work_Date
from   form_Details 
WHERE  Work_Date BETWEEN '2012-01-01' and '2012-01-31' AND Employee_Id=338

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-27 : 02:59:52

if you just need to generate the dates in CSV, you can use a simple while loop or recursive CTE (see below) to do it. It will be faster than accessing from your transactional table.


DECLARE @col VARCHAR(MAX)

; with dates as
(
	select	dte = convert(date, '2012-01-01')

	union all

	select	dte = dateadd(day, 1, dte)
	from	dates
	where	dte	< '2012-01-31'
)
select	@col = coalesce(@col + ',', '') + quotename(dte)
from	dates 

print @col

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources