Hello All,
I'm working on optimizing a SP that is Cursor driven which is not able to
Scale (for obvious
reasons) as the number of records satisfying the Report Criteria increases.
I have been able to re-write the Cursor based SP to a Set based SP to a
large extent. However,
I'm stuck with one aspect for which I might have to use a cursor. Here is
what I have to do :
CREATE TABLE [dbo].[MemberBooks] (
[MemberID] [int] NOT NULL ,
[Book_Description] [varchar] (1000) NULL
) ON [PRIMARY]
GO
insert into MemberBooks(100,'The Bourne Identity')
insert into MemberBooks(100,'The Bourne Legacy')
insert into MemberBooks(100,'The Bourne Supremacy')
insert into MemberBooks(100,'The Bourne Ultimatum')
insert into MemberBooks(200,'Angels and Demons')
insert into MemberBooks(200,'The Main Enemy')
insert into MemberBooks(200,'Globalization and its Discontents')
insert into MemberBooks(200,'The Roaring Nineties')
insert into MemberBooks(400,'Open Secrets')
insert into MemberBooks(400,'Software Release Methodology')
insert into MemberBooks(600,'The Da Vinci Code')
insert into MemberBooks(600,'The coming of the Barbarians')
I need to write a query that would return for each memberid the following :
100, "The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The Bourne
Ultimatum"
200, "Angels and Demons;The Main Enemy;Globalization and its Discontents;The
Roaring Nineties"
400, "Open Secrets;Software Release Methodology"
600, "The Da Vinci Code;The coming of the Barbarians"
I'm know how to get values in a column as a string using the form
select @.Book_Description = @.Book_Description + ';' + Book_Description from
MemberBooks
but that would return all the books which is not what I want, ie,
The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The Bourne
Ultimatum;Angels and Demons;The Main Enemy;Globalization and its
Discontents;The Roaring Nineties;Open Secrets;Software Release
Methodology;The Da Vinci Code;The coming of the Barbarians
However, I cannot use a group by in this to get records of the form
"The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The Bourne
Ultimatum"
"Angels and Demons;The Main Enemy;Globalization and its Discontents;The
Roaring Nineties"
"Open Secrets;Software Release Methodology"
"The Da Vinci Code;The coming of the Barbarians"
Is a Cursor the only choice ?
Gopigopi
Some ideas
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"gopi" <rgopinath@.hotmail.com> wrote in message
news:e0TwPe8IFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I'm working on optimizing a SP that is Cursor driven which is not able to
> Scale (for obvious
> reasons) as the number of records satisfying the Report Criteria
increases.
> I have been able to re-write the Cursor based SP to a Set based SP to a
> large extent. However,
> I'm stuck with one aspect for which I might have to use a cursor. Here is
> what I have to do :
>
> CREATE TABLE [dbo].[MemberBooks] (
> [MemberID] [int] NOT NULL ,
> [Book_Description] [varchar] (1000) NULL
> ) ON [PRIMARY]
> GO
> insert into MemberBooks(100,'The Bourne Identity')
> insert into MemberBooks(100,'The Bourne Legacy')
> insert into MemberBooks(100,'The Bourne Supremacy')
> insert into MemberBooks(100,'The Bourne Ultimatum')
> insert into MemberBooks(200,'Angels and Demons')
> insert into MemberBooks(200,'The Main Enemy')
> insert into MemberBooks(200,'Globalization and its Discontents')
> insert into MemberBooks(200,'The Roaring Nineties')
> insert into MemberBooks(400,'Open Secrets')
> insert into MemberBooks(400,'Software Release Methodology')
> insert into MemberBooks(600,'The Da Vinci Code')
> insert into MemberBooks(600,'The coming of the Barbarians')
>
> I need to write a query that would return for each memberid the following
:
> 100, "The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The
Bourne
> Ultimatum"
> 200, "Angels and Demons;The Main Enemy;Globalization and its
Discontents;The
> Roaring Nineties"
> 400, "Open Secrets;Software Release Methodology"
> 600, "The Da Vinci Code;The coming of the Barbarians"
> I'm know how to get values in a column as a string using the form
> select @.Book_Description = @.Book_Description + ';' + Book_Description from
> MemberBooks
> but that would return all the books which is not what I want, ie,
> The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The Bourne
> Ultimatum;Angels and Demons;The Main Enemy;Globalization and its
> Discontents;The Roaring Nineties;Open Secrets;Software Release
> Methodology;The Da Vinci Code;The coming of the Barbarians
>
> However, I cannot use a group by in this to get records of the form
> "The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The Bourne
> Ultimatum"
> "Angels and Demons;The Main Enemy;Globalization and its Discontents;The
> Roaring Nineties"
> "Open Secrets;Software Release Methodology"
> "The Da Vinci Code;The coming of the Barbarians"
> Is a Cursor the only choice ?
> Gopi
>|||Please, take a look to this post.
[url]http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.pu
blic.sqlserver.programming&mid=e5ed7714-8c9b-41a9-a126-2aac89974053&sloc=en-us[/ur
l]
AMB
"gopi" wrote:
> Hello All,
> I'm working on optimizing a SP that is Cursor driven which is not able to
> Scale (for obvious
> reasons) as the number of records satisfying the Report Criteria increases
.
> I have been able to re-write the Cursor based SP to a Set based SP to a
> large extent. However,
> I'm stuck with one aspect for which I might have to use a cursor. Here is
> what I have to do :
>
> CREATE TABLE [dbo].[MemberBooks] (
> [MemberID] [int] NOT NULL ,
> [Book_Description] [varchar] (1000) NULL
> ) ON [PRIMARY]
> GO
> insert into MemberBooks(100,'The Bourne Identity')
> insert into MemberBooks(100,'The Bourne Legacy')
> insert into MemberBooks(100,'The Bourne Supremacy')
> insert into MemberBooks(100,'The Bourne Ultimatum')
> insert into MemberBooks(200,'Angels and Demons')
> insert into MemberBooks(200,'The Main Enemy')
> insert into MemberBooks(200,'Globalization and its Discontents')
> insert into MemberBooks(200,'The Roaring Nineties')
> insert into MemberBooks(400,'Open Secrets')
> insert into MemberBooks(400,'Software Release Methodology')
> insert into MemberBooks(600,'The Da Vinci Code')
> insert into MemberBooks(600,'The coming of the Barbarians')
>
> I need to write a query that would return for each memberid the following
:
> 100, "The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The Bourn
e
> Ultimatum"
> 200, "Angels and Demons;The Main Enemy;Globalization and its Discontents;T
he
> Roaring Nineties"
> 400, "Open Secrets;Software Release Methodology"
> 600, "The Da Vinci Code;The coming of the Barbarians"
> I'm know how to get values in a column as a string using the form
> select @.Book_Description = @.Book_Description + ';' + Book_Description from
> MemberBooks
> but that would return all the books which is not what I want, ie,
> The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The Bourne
> Ultimatum;Angels and Demons;The Main Enemy;Globalization and its
> Discontents;The Roaring Nineties;Open Secrets;Software Release
> Methodology;The Da Vinci Code;The coming of the Barbarians
>
> However, I cannot use a group by in this to get records of the form
> "The Bourne Identity;The Bourne Legacy;The Bourne Supremacy;The Bourne
> Ultimatum"
> "Angels and Demons;The Main Enemy;Globalization and its Discontents;The
> Roaring Nineties"
> "Open Secrets;Software Release Methodology"
> "The Da Vinci Code;The coming of the Barbarians"
> Is a Cursor the only choice ?
> Gopi
>
>
