Cursor Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cursor Help

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-23 : 12:22:38

I have a cursor which when run displays the following:

NULL NULL NULL NULL
LEVEL0
LEVEL0
LEVEL0
LEVEL0
LEVEL0
LEVEL0
LEVEL1 aa aa aa
LEVEL1 c c c
LEVEL1 b b b
LEVEL1 a a a
LEVEL1 cc cc cc
LEVEL1 bb bb bb

I am trying get it to display one level0 per multiple level1's as shown below. I think I am close, and am hoping someone can tweak the cursor to get it to behave like I want. Code also shown below.
Thanks in advance!

LEVEL0
LEVEL1 a a a
LEVEL1 aa aa aaa
LEVEL0
LEVEL1 b b b
LEVEL1 bb bb bb
LEVEL0
LEVEL1 c c c
LEVEL1 cc cc cc

declare @qValue nvarchar(60), @a varchar(3), @b varchar(3), @c varchar(3)

-- create temp table 1
create table #aTABLE (
a char(3), b char(3), c char(3) )

-- populate temp table
insert into #aTABLE
select 'a', 'a', 'a'
union all
select 'b', 'b', 'b'
union all
select 'c', 'c', 'c'
union all
select 'aa', 'aa', 'aa'
union all
select 'bb', 'bb', 'bb'
union all
select 'cc', 'cc', 'cc'

-- create temp table 2
select 'cola' = @qValue, 'colb' = @qValue, 'colc' = @qValue, 'cold' = @qValue into #aTABLE2

-- create cursor for display
declare TEST cursor for
select a, b, c from #aTABLE
open TEST

fetch next from TEST into @a, @b, @c

while @@fetch_status = 0
begin

insert #aTABLE2(cola, colb, colc, cold)
select 'LEVEL0', '', '', ''

insert #aTABLE2(cola, colb, colc, cold)
select 'LEVEL1', @a, @b, @c

fetch next from TEST into @a, @b, @c
end

close TEST
deallocate TEST

select * from #aTABLE2 order by cola

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-23 : 13:37:28

forget the cursor - how's this:


with cte (cola, colb, colc, cold, grp) as (
       select 'LEVEL1'
              ,a
              ,b
              ,c
              ,left(a,1)
       from   #aTable
       union all
       select distinct 'LEVEL0'
              ,'   '
              ,'   '
              ,'   '
              ,left(a, 1)
       from   #aTable
)
select cola, colb, colc, cold
from   cte
order by grp, colb

OUTPUT:
cola   colb colc cold
------ ---- ---- ----
LEVEL0              
LEVEL1 a    a    a  
LEVEL1 aa   aa   aa 
LEVEL0              
LEVEL1 b    b    b  
LEVEL1 bb   bb   bb 
LEVEL0              
LEVEL1 c    c    c  
LEVEL1 cc   cc   cc

Be One with the Optimizer
TG

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-23 : 13:51:10

Thanks TG!!!!!!!!

The CTE looks good. Let me apply it to my real tables and see how the data looks..

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-23 : 14:38:07

Any chances you can help me with the cursor route?
It would be nice to see how each one compares...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-26 : 10:55:08

Ok - please report back any performance differences as well as how many rows in your tables. This output is identical with the cte solution based on the data in your original post.


set nocount on
declare @qValue nvarchar(60), @a varchar(3), @b varchar(3), @c varchar(3)

-- create temp table 1
create table #aTABLE (
a char(3), b char(3), c char(3) )

-- populate temp table
insert into #aTABLE
select 'a', 'a', 'a'
union all
select 'b', 'b', 'b'
union all
select 'c', 'c', 'c'
union all
select 'aa', 'aa', 'aa'
union all
select 'bb', 'bb', 'bb'
union all
select 'cc', 'cc', 'cc'

-- create temp table 2
select 'cola' = @qValue, 'colb' = @qValue, 'colc' = @qValue, 'cold' = @qValue, 'grp' as grp into #aTABLE2 where 1=2

-- create cursor for display
declare TEST cursor for
select a, b, c from #aTABLE
open TEST

fetch next from TEST into @a, @b, @c

while @@fetch_status = 0
begin

if left(@a,1) = @a
begin
       insert #aTABLE2(cola, colb, colc, cold, grp)
       select 'LEVEL0', '', '', '', @a
end

insert #aTABLE2(cola, colb, colc, cold, grp)
select 'LEVEL1', @a, @b, @c, left(@a,1)

fetch next from TEST into @a, @b, @c
end

close TEST
deallocate TEST

select cola, colb, colc, cold from #aTABLE2 order by grp, colb

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources