使用alter命令将列显示为行 [英] Displaying the columns as rows using alter command

问题描述

亲爱的所有人，

我将我的问题稍加修改后重新发布，作为我的疑问..！

请查看以下存储的过程.

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

--------

我只想使用"alter table"命令将列作为行.
例如;现在其显示如下:
NewDate经审核的总数
2011年8月1日0
2011年8月2日1

但我想要的结果是:
NewDate 8/1/2011 8/2/1011
审核总数0 1

请任何人帮助我..

问候，

--------

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,

推荐答案

看来您应该使用PIVOT运算符来获取所需的内容.不幸的是，PIVOT运算符要求您事先知道要旋转的列，这意味着您应该在表中运行游标并创建一些动态SQL并执行它.此过程是非破坏性的，即您完全不会更改表.如果需要，可以创建另一个存储过程来获取结果集.

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.

这篇关于使用alter命令将列显示为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！