Concating values of a Column based on a Group

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
>
>

Concating text columns

I have a table with a text column
I want to select that column and add some text to the result
e.g.
select textcolumn + 'Added Text' From xTable

but this fails because one can not use + or concat on text columns
i get around this by using cast as varchar(1024)
e.g.
select cast(textcolumn as varchar(1024)) + 'Added Text' From xTable

but this would cut the text column after 1024 chars

is there a way around this and not limiting the length of the text columnI'm at home and not a work at the moment so I can't check for sure (or rather I'm not going to), but can't you omit the length of the varchar in the cast statement??|||select cast(textcolumn as varchar) + 'Added Text' From xTable
or
select convert(varchar, textcolumn) + 'Added Text' From xTable

works for my SQL2000, otherwise, you can just trim out the trailing spaces|||Just be aware that the max length of varchar is 8000.

Originally posted by shianmiin
select cast(textcolumn as varchar) + 'Added Text' From xTable
or
select convert(varchar, textcolumn) + 'Added Text' From xTable

works for my SQL2000, otherwise, you can just trim out the trailing spaces

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advance
perspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4
|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
>
>
>
>
>
>
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com

concating columns

this is my DDL:
CREATE TABLE [dbo].[Table1] (
[Code] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ParentCode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
(
[ParentCode]
) REFERENCES [dbo].[Table1] (
[Code]
)
I want to concat Column of Name:
Code Name ParentCode
1 test NULL
2 book NULL
3 Cake 1
4 Mouse 3
I want to concat column of Name for Code=4 and output will be: testCake
thanks in advanceperspolis wrote:
> this is my DDL:
> CREATE TABLE [dbo].[Table1] (
> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ParentCode] [int] NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> (
> [Code]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> (
> [ParentCode]
> ) REFERENCES [dbo].[Table1] (
> [Code]
> )
> I want to concat Column of Name:
> Code Name ParentCode
> 1 test NULL
> 2 book NULL
> 3 Cake 1
> 4 Mouse 3
> I want to concat column of Name for Code=4 and output will be: testCake
> thanks in advance
SELECT c.[Name] + b.[Name] AS ConcatName
FROM Table1 AS a
JOIN Table1 AS b ON a.ParentCode = b.Code
JOIN Table1 AS c ON b.ParentCode = c.Code
WHERE a.Code = 4|||I want to do that for many levels as is not for 2 rows.
"Ed Enstrom" <nospam@.invalid.net> wrote in message
news:np8Zh.98$eH4.18@.newsfe12.lga...
> perspolis wrote:
>> this is my DDL:
>> CREATE TABLE [dbo].[Table1] (
>> [Code] [int] IDENTITY (1, 1) NOT NULL ,
>> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [ParentCode] [int] NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>> (
>> [Code]
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[Table1] ADD
>> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
>> (
>> [ParentCode]
>> ) REFERENCES [dbo].[Table1] (
>> [Code]
>> )
>> I want to concat Column of Name:
>> Code Name ParentCode
>> 1 test NULL
>> 2 book NULL
>> 3 Cake 1
>> 4 Mouse 3
>> I want to concat column of Name for Code=4 and output will be: testCake
>> thanks in advance
> SELECT c.[Name] + b.[Name] AS ConcatName
> FROM Table1 AS a
> JOIN Table1 AS b ON a.ParentCode = b.Code
> JOIN Table1 AS c ON b.ParentCode = c.Code
> WHERE a.Code = 4
>
>|||On Apr 30, 9:13 am, "perspolis" <reza...@.hotmail.com> wrote:
> I want to do that for many levels as is not for 2 rows.
> "Ed Enstrom" <nos...@.invalid.net> wrote in message
> news:np8Zh.98$eH4.18@.newsfe12.lga...
>
> > perspolis wrote:
> >> this is my DDL:
> >> CREATE TABLE [dbo].[Table1] (
> >> [Code] [int] IDENTITY (1, 1) NOT NULL ,
> >> [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> >> [ParentCode] [int] NULL
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
> >> (
> >> [Code]
> >> ) ON [PRIMARY]
> >> GO
> >> ALTER TABLE [dbo].[Table1] ADD
> >> CONSTRAINT [FK_Table1_Table1] FOREIGN KEY
> >> (
> >> [ParentCode]
> >> ) REFERENCES [dbo].[Table1] (
> >> [Code]
> >> )
> >> I want to concat Column of Name:
> >> Code Name ParentCode
> >> 1 test NULL
> >> 2 book NULL
> >> 3 Cake 1
> >> 4 Mouse 3
> >> I want to concat column of Name for Code=4 and output will be: testCake
> >> thanks in advance
> > SELECT c.[Name] + b.[Name] AS ConcatName
> > FROM Table1 AS a
> > JOIN Table1 AS b ON a.ParentCode = b.Code
> > JOIN Table1 AS c ON b.ParentCode = c.Code
> > WHERE a.Code = 4- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 CTE with recursive query.
with temp as
(select convert(varchar(50),'') + convert(varchar(50),'')
name ,parentcode,code from table1 where code =4
union all
select convert(varchar(50),t1.name)+convert(varchar(50),t.name) as
name ,t1.parentcode,t1.code
from table1 t1 inner join temp t on t.parentcode = t1.code)
select name from temp where parentcode is null
Regards
Amish shah
http://shahamishm.tripod.com