Data Replication in Concatenating using While Loop

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

Transact-SQL (2005)

Data Replication in Concatenating using While Loop

Author

Topic

kalaiselvan.rajendran
Starting Member

2 Posts

Posted - 2010-02-01 : 08:35:08

ALTER proc [dbo].[timereportmodified]
(
@StartDate as DateTime,
@UserId AS Int
)
AS
BEGIN
DECLARE @Enddate DateTime;
SET @Enddate=dateadd(DAY,6,@StartDate);
DECLARE @nvarQuery nvarchar(MAX);

SET @nvarQuery='SELECT
[AppointmentId] As [Project/Date], ';
WHILE (@StartDate <=@EndDate)
BEGIN
SET @nvarQuery=@nvarQuery+'[TotHrs]
AS ['+CONVERT(varchar(9),@StartDate,6)+'],[description] AS [Task],';
SET @StartDate =DateAdd(d,1,@StartDate);
END
SET @nvarQuery=SUBSTRING(@nvarQuery,0,LEN(@nvarQuery))+'
FROM
timsheetsnewview
WHERE
UserId='+CAST(@UserId AS VARCHAR)+'
AND
CAST([St] AS VARCHAR) BETWEEN '''+ CAST(@StartDate AS varchar) + ''' AND '''+ CAST(@Enddate AS varchar) + '''
Group By [AppointmentId],[TotHrs],Description';
--select @nvarQuery;
EXEC SP_EXECUTESQL @nvarQuery;
END

*Thats the above Query. In this Timesheetnewview is a View created using TimeSheet Table.
*Using While Loop and Concatenation The data have binded as Rows into Columns.
*But the data is repeating in columns for each date. The Task and Total Hours have been repeated for each projects. I will post the result here.
*Please check it out and reply me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 08:43:15

are you trying for dynamic pivot? if yes, use below

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources