[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Use cursor to append row values?

Posted on 2007-07-30
8
Medium Priority
?
1,122 Views
Last Modified: 2010-05-18
I have a table like this (abridged):
Alert Period                  Department        AlertSetting
Down at least 1 day      DB      E-mail
Down at least 1 day      DB      SMS
Down at least 1 day      IT      E-mail
Down at least 1 day      IT      SMS
Down at least 1 day      QAG      E-mail
Down at least 1 day      QAG      SMS
......

I want to append the values in each row for each comination of Alert Period and Department like this:
Down at least 1 day      DB      E-mail/SMS
Down at least 1 day      IT      E-mail/SMS
Down at least 1 day      QAG      E-mail/SMS
......

I wrote a cursor like this, but the result I got is like:

Down at least 1 day      DB      SMS/SMS
Down at least 1 day      IT      NULL
Down at least 1 day      QAG      NULL

Can someone please tell me where I was wrong in the code?
Thanks a lot!!
------code------
declare @counter int,
        @count int
declare @AlertPeriod nvarchar(150),
        @Department nvarchar(50),
        @AlertSetting nvarchar(100)

create table #Rotate
             (AlertPeriod nvarchar(150),
              Department  nvarchar(50),
              AlertSetting nvarchar(100))

select @counter = 0

declare mycursor cursor
for select distinct [Alert Period], Department
from #ForCursor

open mycursor
fetch next from mycursor
into @AlertPeriod, @Department

while @@fetch_status = 0
begin
     select @count = count(*)
     from #ForCursor
     where [Alert Period] = @AlertPeriod
     and   Department = @Department

while @counter <= @count
begin
     select @AlertSetting = AlertSetting + '/' + AlertSetting
     from #ForCursor
     where [Alert Period] = @AlertPeriod
     and   Department = @Department

     set @counter = @counter + 1
end

insert into #Rotate
values (@AlertPeriod, @Department, @AlertSetting)

set @AlertSetting = null

fetch next from mycursor
into @AlertPeriod, @Department
end

close mycursor
deallocate mycursor
0
Comment
Question by:qinyan
8 Comments
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 300 total points
ID: 19593742
declare mycursor cursor
for select distinct [Alert Period], Department
from #ForCursor

open mycursor
fetch next from mycursor
into @AlertPeriod, @Department

while @@fetch_status = 0
begin
     select @count = count(*)
     from #ForCursor
     where [Alert Period] = @AlertPeriod
     and   Department = @Department
--Use emptry string instead of NULL since adding any string to a NULL string produces a NULL string
SELECT @AlertSetting = ''
while @count > 0
begin
     select @AlertSetting = @AlertSetting + AlertSetting + '/'
     from #ForCursor
     where [Alert Period] = @AlertPeriod
     and   Department = @Department
   --Strip off the trailing '/'
     SELECT @AlertSetting = SUBSTRING(@AlertSetting, 1, LEN(@AlertSetting) - 1)
     SET @Count = @Count - 1
end

insert into #Rotate
values (@AlertPeriod, @Department, @AlertSetting)

fetch next from mycursor into @AlertPeriod, @Department
end

close mycursor
deallocate mycursor
0
 
LVL 3

Assisted Solution

by:vilimed
vilimed earned 300 total points
ID: 19593802
declare @counter int,
        @count int
declare @AlertPeriod nvarchar(150),
@AlertPeriodPrev nvarchar(150),
        @Department nvarchar(50),
        @DepartmentPrev nvarchar(50),
        @AlertSetting nvarchar(100),
@AlertSettingSum nvarchar(100)

create table #Rotate
             (AlertPeriod nvarchar(150),
              Department  nvarchar(50),
              AlertSetting nvarchar(100))


declare mycursor cursor
for select [Alert Period], Department, AlertSetting
from #ForCursor
order by [Alert Period], Department

open mycursor
fetch next from mycursor
into @AlertPeriod, @Department, @AlertSetting

if @@fetch_status = 0 then
begin
select @DepartmentPrev = @DepartmentPrev, @AlertPeriodPrev = @AlertPeriod, @AlertSettingSum  =''
end
while @@fetch_status = 0
begin

insert into #Rotate
values (@AlertPeriod, @Department, @AlertSetting)

if (@AlertPeriod = @AlertPeriodPrev and @Department = @DepartmentPrev)
then
begin
if (@AlertSettingSum ='')
then
begin
select @AlertSettingSum = @AlertSetting
end else
begin
select @AlertSettingSum = @AlertSettingSum  + '/' + @AlertSetting
end
else
begin
insert into #Rotate
values (@AlertPeriod, @Department, @AlertSettingSum)
select @DepartmentPrev = @DepartmentPrev, @AlertPeriodPrev = @AlertPeriod, @AlertSettingSum  =''
end

end
if(@AlertSettingSum<>'')
then
begin
insert into #Rotate
values (@AlertPeriod, @Department, @AlertSettingSum)
end
fetch next from mycursor
into @AlertPeriod, @Department, @AlertSetting
end

close mycursor
deallocate mycursor
0
 
LVL 8

Accepted Solution

by:
k_rasuri earned 600 total points
ID: 19593826
here the whole statement that i pasted below is wrong...............
while @counter <= @count
begin
     select @AlertSetting = AlertSetting + '/' + AlertSetting
     from Alerts
     where [AlertPeriod] = @AlertPeriod
     and   Department = @Department

in the first case....your cursor read the first records from #forcursor table...
based on 'select @AlertSetting = AlertSetting + '/' + AlertSetting' statement,, your cursor inserted the values at both the 'altertsetting' places...so what you see is SMS/SMS

in the second and third case...your count(*) is set to 2...
you intialized the counter to 0. as you said @counter<=@count...your counter has 3 iterations to run.
but your source table has only two records...so the final record just came up as NULLs..

this is just the explanation why your cursor went wrong...you might have already found the solutions in the above posts
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 22

Expert Comment

by:dportas
ID: 19594051
There are several possible ways to write this query without a cursor. Here's one:

SELECT AlertPeriod, Department,
 SUBSTRING(
  MAX(CASE WHEN AlertSetting = 'E-mail' THEN '/E-mail' ELSE '' END)+
  MAX(CASE WHEN AlertSetting = 'SMS' THEN '/SMS' ELSE '' END)
  ,2,100)
 AS AlertSetting
 FROM YourTable
 GROUP BY AlertPeriod, Department;

0
 

Author Comment

by:qinyan
ID: 19594134
Thanks all for your reply!
For BriCrowe: the result I got is like this, so it's repeating it once:
Down at least 1 day      DB      E-mail/SMSE-mail/SMS
Down at least 1 day      IT      E-mail/SMSE-mail/SMS
Down at least 1 day      QAG      E-mail/SMSE-mail/SMS

For vilimed:
I got the following message:
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Line 42
Incorrect syntax near the keyword 'else'.
Msg 156, Level 15, State 1, Line 46
Incorrect syntax near the keyword 'else'.
Msg 156, Level 15, State 1, Line 55
Incorrect syntax near the keyword 'then'.
0
 

Author Comment

by:qinyan
ID: 19594384
dportas,
Thanks and I like your method which is neat. I use cursor because the number of alert settings are dynamic I think. Right now there are two. What if in the future we have 3, 4 or more? Then I have to manually add that to the case statement. With a cursor, the number or type of alert settings doesn't matter and the appended result automatically changes.
Thanks again!
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 300 total points
ID: 19594475
Don't assume you'll need a cursor even if the number of values is unknown. There are a few more solutions here:
http://blogs.conchango.com/jamiethomson/archive/2007/03/20/T_2D00_SQL_3A00_-Produce-a-comma-delimited-list-of-values-from-a-rowset.aspx
0
 

Author Comment

by:qinyan
ID: 20841231
Thanks for all the good suggestions!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question