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
I don't understand what you're trying to accomplish. Can you explain the situation a little more?
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
EndPlzz Plzz Plzzz Help me out.....Thanks!
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
EndAs 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.....
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 #AVWe're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.