0

I want to display the columns in to rows format as below:

eg: col 1 clo 2
yes No

I want this to display as:

Column name
yes
no

The Code which i used as below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[Vista_Capable]        
AS              
SET NOCOUNT ON;              
BEGIN
           Select 'Yes','No'
End

Edited by peter_budo: Keep It organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

2
Contributors
4
Replies
5
Views
8 Years
Discussion Span
Last Post by sknake
0

I don't understand what you're trying to accomplish. Can you explain the situation a little more?

0

I have to create a report like this.

Date Range Count
<1 20
1-7 167
8-15 15
16-30 61
31-60 7
>60 87
Never 71

For this I've created a Store procedure which display as below:

<1 1-7 8-15 16-30 31-60 >60 Never
107 0 1 24 248 87 71

So how should I do this. The Store Procedure which I've created is as below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

Alter PROCEDURE [dbo].[Security_Defination_Update]        
AS              
SET NOCOUNT ON;              
BEGIN

declare @temp1 as int
declare @temp as datetime
declare @var1 as int,@var2 as int,@var3 as int,@var4 as int,@var5 as int,@var6 as int,@var7 as int
set @temp1=0
set @var1=0
set @var2=0
set @var3=0
set @var4=0
set @var5=0
set @var6=0
set @var7=0

declare abc cursor scroll for select   Pubdate from antivirus
open abc
fetch next from abc into @temp
while @@fetch_status=0
Begin
         set @temp1 = datediff(day,@temp,getdate())
             if (@temp1<1)  set @var1 = @var1+1 
             else
               if (@temp1>=1 and @temp1<=7)  set @var2= @var2+1
             ELSE 
               if (@temp1>7 and @temp1<=15)  set @var3 =@var3+1
             ELSE 
               if (@temp1>15 and @temp1<=30)  set @var4=@var4+1
             ELSE 
               if (@temp1>30 and @temp1<=60)  set @var5 = @var5+1
             ELSE 
               if (@temp1>60)   set @var6 = @var6+1
             ELSE
               if (@temp1 IS NULL) set @var7 = @var7 + 1		
fetch next from abc into @temp
END
close abc

select @var1 '<1',@var2'1-7',@var3'8-15',@var4'16-30',@var5'31-60',@var6'>60',@var7'Never'
deallocate abc

End

Plzz Plzz Plzzz Help me out.....Thanks!

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

-1

I have created a Store Procedure as per below displayed report:. I want the Store Procedure to be develope as below:
<1 1-7 8-15 16-30 31-60 >60 Never
Defination-
Update 20 167 15 61 7 87 71
Virus Scan 49 90 20 52 19 87 111

Vulnerability-
Scan 155 111 8 13 4 79 58

Spyware Scan 0 0 0 0 1 110 317

Store Procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Security_Status_Summary]        
AS              
SET NOCOUNT ON;              
BEGIN

declare @temp1 as int,@temp2 as int,@temp3 as int,@temp4 as int
declare @temp as datetime
declare @var1 as int,@var2 as int,@var3 as int,@var4 as int,@var5 as int,@var6 as int,@var7 as int
set @temp1=0
set @temp2=0
set @temp3=0	
set @temp4=0
set @var1=0
set @var2=0
set @var3=0
set @var4=0
set @var5=0
set @var6=0
set @var7=0
-----------------------PubDate-------------------------------
declare abc cursor scroll for select   Pubdate from antivirus
open abc
fetch next from abc into @temp
while @@fetch_status=0
Begin

	set @temp1 = datediff(day,@temp,getdate())
    if (@temp1<1)  set @var1 = @var1+1 
	 else
        if (@temp1>=1 and @temp1<=7)  set @var2= @var2+1
	 ELSE 
		if (@temp1>7 and @temp1<=15)  set @var3 =@var3+1
	 ELSE 
		if 	(@temp1>15 and @temp1<=30)  set @var4=@var4+1
	 ELSE 
		if (@temp1>30 and @temp1<=60)  set @var5 = @var5+1
	 ELSE 
		if(@temp1>60)   set @var6 = @var6+1
	 ELSE
		if (@temp1 IS NULL) set @var7 = @var7 + 1		

fetch next from abc into @temp
END
close abc

select 'Definition Publish Date', @var1 '<1',@var2'1-7',@var3'8-15',@var4'16-30',@var5'31-60',@var6'>60',@var7'Never'
deallocate abc

-----------------------LastVirusScan-----------------------------
declare pqr cursor scroll for select LastVirusScan from antivirus
open pqr

fetch next from pqr into @temp
while @@fetch_status=0
begin 
	set @temp2 = datediff(day,@temp,getdate())
	if (@temp2 < 1) set @var1 = @var1+1
		else 
			if (@temp2>=1 and @temp2<=7) set @var2 = @var2+1
		else 
			if 	(@temp2>7 and @temp2<=15) set @var3 = @var3+1
		else
			if (@temp2>15 and @temp2<=30) set @var4 = @var4 + 1
		else
			if (@temp2>31 and @temp2<=60) set @var5 = @var5+1
		else 
			if (@temp2>60) set @var6 = @var6+1
		ELSE
			if (@temp2 IS NULL) set @var7 = @var7 + 1		

fetch next from pqr into @temp
end 
close pqr

select 'Virus Scan', @var1 '<1',@var2'1-7',@var3'8-15',@var4'16-30',@var5'31-60',@var6'>60',@var7'Never'
deallocate pqr

----------------------VALastScanDate--------------------------------------------
declare xyz cursor scroll for select VALastScanDate from computer
open xyz

fetch next from xyz into @temp
while @@fetch_status=0
begin 
	set @temp3 = datediff(day,@temp,getdate())
	if (@temp3 < 1) set @var1 = @var1+1
		else 
			if (@temp3>=1 and @temp3<=7) set @var2 = @var2+1
		else 
			if 	(@temp3>7 and @temp3<=15) set @var3 = @var3+1
		else
			if (@temp3>15 and @temp3<=30) set @var4 = @var4 + 1
		else
			if (@temp3>31 and @temp3<=60) set @var5 = @var5+1
		else 
			if (@temp3>60) set @var6 = @var6+1
		ELSE
			if (@temp3 IS NULL) set @var7 = @var7 + 1		


fetch next from xyz into @temp
end 
close xyz

select 'Vulnerability Scan', @var1 '<1',@var2'1-7',@var3'8-15',@var4'16-30',@var5'31-60',@var6'>60',@var7'Never'
deallocate xyz 
---------------------SpywareLastScanDate-----------------------------------
declare mno cursor scroll for select SpywareLastScanDate from computer
open mno

fetch next from mno into @temp
while @@fetch_status=0
begin 
	set @temp4 = datediff(day,@temp,getdate())
	if (@temp4 < 1) set @var1 = @var1+1
		else 
			if (@temp4>=1 and @temp4<=7) set @var2 = @var2+1
		else 
			if 	(@temp4>7 and @temp4<=15) set @var3 = @var3+1
		else
			if (@temp4>15 and @temp4<=30) set @var4 = @var4 + 1
		else
			if (@temp4>31 and @temp4<=60) set @var5 = @var5+1
		else 
			if (@temp4>60) set @var6 = @var6+1
		ELSE
			if (@temp4 IS NULL) set @var7 = @var7 + 1		
fetch next from mno into @temp
end 
close mno

select 'Spyware Scan', @var1 '<1',@var2'1-7',@var3'8-15',@var4'16-30',@var5'31-60',@var6'>60',@var7'Never'
deallocate mno

End

As per above Store Procedure, it is CORRECT, but it is displayng the report one below the another. As there are each select select statement for for each Scanning Column. I tried by using PIVOT Function but I didn't get it.....Need help on thid......ITS VERY URGENNNTTTT.....

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

Votes + Comments
duplicate posts
0

Just pivot the table by hand:

IF OBJECT_ID('tempdb..#AV', 'U') IS NOT NULL DROP TABLE #AV
Create Table #AV
(
  RecordId int identity(1000, 1) PRIMARY KEY,
  PubDate DateTime
)

SET NOCOUNT ON

Declare @i1 int
Set @i1 = -50
WHILE (@i1 <= 100)
BEGIN
  Insert Into #AV (PubDate) Values (GetDate()-@i1)
  Set @i1 = @i1 + 1
END

SET NOCOUNT OFF

Select 
Sum((Case When DateDiff(day, PubDate, GetDate()) < 1 Then 1 Else 0 End)) As [<1],
Sum((Case When DateDiff(day, PubDate, GetDate()) >= 1 and DateDiff(day, PubDate, GetDate()) <= 7 Then 1 Else 0 End)) As [1-7],
Sum((Case When DateDiff(day, PubDate, GetDate()) > 7 and DateDiff(day, PubDate, GetDate()) <= 15 Then 1 Else 0 End)) As [7-15],
Sum((Case When DateDiff(day, PubDate, GetDate()) > 15 and DateDiff(day, PubDate, GetDate()) <= 30 Then 1 Else 0 End)) As [15-30],
Sum((Case When DateDiff(day, PubDate, GetDate()) > 30 and DateDiff(day, PubDate, GetDate()) <= 60 Then 1 Else 0 End)) As [30-60],
Sum((Case When DateDiff(day, PubDate, GetDate()) > 60 Then 1 Else 0 End)) As [60+],
Sum((Case When DateDiff(day, PubDate, GetDate()) Is Null Then 1 Else 0 End)) As Nulls
From #AV
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.