troubleshooting Question

Use cursor to append row values?

Avatar of qinyan
qinyan asked on
Microsoft SQL Server
8 Comments4 Solutions1151 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 4 Answers and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros