Click here to Skip to main content
15,907,395 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

I am reposting my question with a small modification as my doubt..!

Kindly have look at the following stored proc.

ALTER PROCEDURE  [dbo].[BINDMONTHSFORDAY] --7
   (
           @Day  BIGINT
   )
AS
SET NOCOUNT ON
DECLARE @CurrentDate DATETIME
DECLARE @FirstDate DATETIME
DECLARE @LastDate DATETIME
DECLARE @AuditTotal BIGINT

DECLARE @Today DATETIME
DECLARE @Months TABLE 
(
NewDate DATETIME,
AuditTotal BIGINT
)
DECLARE @Year BIGINT
IF (@Day < 1 OR @Day > 31)
BEGIN
   RAISERROR('Invalid day specified for date function', 16, 1)
   RETURN
END
SET @Today = GETDATE()
SET @Year = DATEPART(yyyy, @Today )
SET @FirstDate = CAST(CAST(1 AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)
SET @LastDate = CAST(CAST(12 AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)
SET @CurrentDate = @FirstDate
While @CurrentDate <= @LastDate
Begin
Insert Into @Months Values(@CurrentDate,@AuditTotal)
	IF (DATEPART(dd, @CurrentDate) != @Day)
	BEGIN
	Set @CurrentDate = DateAdd(m,1,@CurrentDate)
	SET @CurrentDate = CAST(CAST(DATEPART(mm, @CurrentDate) AS varchar) + '-' + CAST(@Day AS varchar) + '-' + CAST(@Year AS varchar) AS DATETIME)
	END
	ELSE
	BEGIN
	Set @CurrentDate = DateAdd(m,1,@CurrentDate)
	END
	
update @Months set AuditTotal=(select COUNT(A.Uid)  FROM AuditFlags A WHERE A.CPPMatch IS NOT NULL AND
    CONVERT(NVARCHAR(50),A.CreatedOn,103)= convert( NVARCHAR(50), B.NewDate, 103 ) AND HoldStatus=0) from @Months
		
End
select convert( varchar(50), NewDate, 103 ) as 'NewDate',AuditTotal AS 'Total Audited' FROM @Months

RETURN @@ERROR
SET NOCOUNT OFF

--------

I want to use only the "alter table" command to get the columns as rows.
E.g; now its displaying as below:
NewDate Total Audited
8/1/2011 0
8/2/1011 1

But I want the result as:
NewDate 8/1/2011 8/2/1011
Total Audited 0 1

Please anyone help me on this..

Regards,
Posted
Updated 8-Mar-11 1:01am
v2

1 solution

It looks like you should use the PIVOT operator to get what you want. Unfortunately the PIVOT operator requires that you know in advance the columns on which you wish to pivot, which means that you should run a cursor through the table and create some dynamic SQL and execute it. This procedure is non destructive, i.e. you do not alter the table at all this way. If you must, you can create another stored procedure to get your result set.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900