looping through a recordset

Hi
I am a recordset which I would like to extract a field and make a string
from it, by appending values to it.
e.g
PolicyRef Product
C001 M
C001 B
C001 S
C002 N
C002 C
C002 T
Ideally, what I need is the products in one field:
e.g
PolicyRef Product
C001 M/B/S
C002 N/C/T
I am trying to create a Loop construct, but not having much luck.
Any ideas?
Kind Regards
RickyIf you are using SQL Server 2005 you can do this...
(taken from my blog entry:
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
create table mailing_list (
individual_name nvarchar(100) not null,
list_name nvarchar(10) not null
)
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )
select distinct
individual_name,
list = substring(
( select '/' + list_name as [text()]
from mailing_list m2
where m2.individual_name = m1.individual_name
for xml path(''), elements )
, 2, 100 )
from mailing_list m1
gives...
alex r List A
joe r List A/List B
tony r List A/List B/List C
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> Hi
> I am a recordset which I would like to extract a field and make a string
> from it, by appending values to it.
> e.g
> PolicyRef Product
> C001 M
> C001 B
> C001 S
> C002 N
> C002 C
> C002 T
> Ideally, what I need is the products in one field:
> e.g
> PolicyRef Product
> C001 M/B/S
> C002 N/C/T
> I am trying to create a Loop construct, but not having much luck.
> Any ideas?
> Kind Regards
> Ricky
>|||Hi Tony
Thanks for your reply, is there something more dynamic, I've been told to
try and use a WHILE loop and use the ROWCOUNT, but not sure how to implment
this?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> If you are using SQL Server 2005 you can do this...
> (taken from my blog entry:
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> create table mailing_list (
> individual_name nvarchar(100) not null,
> list_name nvarchar(10) not null
> )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
> C' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> A' )
> insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
> B' )
> insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
> A' )
> select distinct
> individual_name,
> list = substring(
> ( select '/' + list_name as [text()]
> from mailing_list m2
> where m2.individual_name = m1.individual_name
> for xml path(''), elements )
> , 2, 100 )
> from mailing_list m1
> gives...
> alex r List A
> joe r List A/List B
> tony r List A/List B/List C
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:O6Kep5cmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>|||What version of SQL Server are you using? If you are using 2005 then use the
FOR XML below because the WHILE loops are iterative whereas the FOR XML is
set orientated so will perform signifcantly better.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
> Hi Tony
> Thanks for your reply, is there something more dynamic, I've been told to
> try and use a WHILE loop and use the ROWCOUNT, but not sure how to
> implment
> this?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23m4EDAdmGHA.2204@.TK2MSFTNGP03.phx.gbl...
> SQL
>|||I'm using SS2K..
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> What version of SQL Server are you using? If you are using 2005 then use
the
> FOR XML below because the WHILE loops are iterative whereas the FOR XML is
> set orientated so will perform signifcantly better.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:Oe%23bOEdmGHA.5040@.TK2MSFTNGP04.phx.gbl...
to
'List
'List
a
>|||Ok, here you go....
The basis of this is that you don't have that many Product's so you can
dynamically build the SQL using a cursor, so the performance comes from
orders of magnitude, for instance - far more performant to run around a
cursor once for just 5 rows then to run round a cursor once for every single
policy ref!
I'll leave you with figuring out how to get rid of the '/' on the end of the
string, should just be substring and datalength - if you get stuck then post
back.
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> I'm using SS2K..
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eTkAmQdmGHA.4076@.TK2MSFTNGP03.phx.gbl...
> the
> SQL
> to
> 'List
> 'List
> a
>|||Ok, so rather than leave the job half done...
declare cur cursor for
select distinct Product
from SourceData
declare @.product char(1)
declare @.sql_case varchar(8000)
set @.sql_case = ''
open cur
fetch next from cur into @.product
while @.@.fetch_status = 0
begin
set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
end +
'case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = ''' + @.Product +
''' ) then '''+ @.Product + '/'' else '''' end'
fetch next from cur into @.product
end
deallocate cur
declare @.sql varchar(8000)
set @.sql = '
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = ' + @.sql_case + '
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef'
print @.sql
exec( @.sql )
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
> single policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
> the string, should just be substring and datalength - if you get stuck
> then post back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
>|||Hi Tony
Thank you for the posting, will there be a performance issue, if this is run
for many different policies?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> Ok, here you go....
> The basis of this is that you don't have that many Product's so you can
> dynamically build the SQL using a cursor, so the performance comes from
> orders of magnitude, for instance - far more performant to run around a
> cursor once for just 5 rows then to run round a cursor once for every
single
> policy ref!
> I'll leave you with figuring out how to get rid of the '/' on the end of
the
> string, should just be substring and datalength - if you get stuck then
post
> back.
> declare cur cursor for
> select distinct Product
> from SourceData
> declare @.product char(1)
> declare @.sql_case varchar(8000)
> set @.sql_case = ''
> open cur
> fetch next from cur into @.product
> while @.@.fetch_status = 0
> begin
> set @.sql_case = @.sql_case + case when @.sql_case = '' then '' else '+'
> end +
> 'case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = ''' + @.Product +
> ''' ) then '''+ @.Product + '/'' else '''' end'
> fetch next from cur into @.product
> end
> deallocate cur
> declare @.sql varchar(8000)
> set @.sql = '
> select PolicyRef,
> Concat = ' + @.sql_case + '
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> order by PolicyRef'
> print @.sql
> exec( @.sql )
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:echGrXdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
use
a
told
http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
from
>|||The actual SQL will produce this which is what does the work...
How many Products do you have?
This SQL will beat cursor or looping by orders of magnitude - try it.
Was the DDL you posted accurate to your own system, if not then best post
the DDL (including indexes) and I'll check to see if you'll get a good
plan...
select PolicyRef,
Concat = substring( Concat, 1, len( Concat ) - 1 )
from (
select PolicyRef,
Concat = case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'B' ) then 'B/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'C' ) then 'C/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'M' ) then 'M/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'N' ) then 'N/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'S' ) then 'S/' else
'' end+case when exists ( select *
from SourceData s
where s.PolicyRef = p.PolicyRef
and s.Product = 'T' ) then 'T/' else
'' end
from (
select PolicyRef
from SourceData
group by PolicyRef ) as p
) as c
order by PolicyRef
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"ricky" <ricky@.ricky.com> wrote in message
news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
> Hi Tony
> Thank you for the posting, will there be a performance issue, if this is
> run
> for many different policies?
> Kind Regards
> Ricky
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eUYL3idmGHA.4032@.TK2MSFTNGP02.phx.gbl...
> single
> the
> post
> SQL
> use
> a
> told
> http://sqlblogcasts.com/blogs/tonyr.../05/11/429.aspx)
> from
>|||Hi Tony
The amount of products in a policy can range from anywhere to 1 (default) to
about 5?
Kind Regards
Ricky
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%237tx4pdmGHA.1404@.TK2MSFTNGP05.phx.gbl...
> The actual SQL will produce this which is what does the work...
> How many Products do you have?
> This SQL will beat cursor or looping by orders of magnitude - try it.
> Was the DDL you posted accurate to your own system, if not then best post
> the DDL (including indexes) and I'll check to see if you'll get a good
> plan...
> select PolicyRef,
> Concat = substring( Concat, 1, len( Concat ) - 1 )
> from (
> select PolicyRef,
> Concat = case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'B' ) then 'B/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'C' ) then 'C/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'M' ) then 'M/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'N' ) then 'N/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'S' ) then 'S/'
else
> '' end+case when exists ( select *
> from SourceData s
> where s.PolicyRef = p.PolicyRef
> and s.Product = 'T' ) then 'T/'
else
> '' end
> from (
> select PolicyRef
> from SourceData
> group by PolicyRef ) as p
> ) as c
> order by PolicyRef
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
SQL
> Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "ricky" <ricky@.ricky.com> wrote in message
> news:%23D2jhldmGHA.492@.TK2MSFTNGP05.phx.gbl...
of
'+'
a
from
r',
r',
a
luck.
>