-
? on how add strings together
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
-
? on how add strings together (reply)
cant seem to get my head around this one. but here is a cursor SP you can use with an input using the tindex. this isnt the most efficent way to do this, and i hate cursors, but here it is anyway. HTH
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
Name: GetFullDescription
Type: TSQL SP
Purpose: concat all related descriptions
Author: Matthew B. Nye
Created: 08.22.02
Modified:
Inputs/Outputs: Input related tindex
.:mbn|aug|mmii:.
*/
CREATE PROC GetFullDescription
@tindex int
AS
DECLARE
@desc varchar(25)
,@FullDesc varchar(1000)
DECLARE TestLoop CURSOR
FOR
SELECT description
FROM TEST
WHERE tindex = @tindex
OPEN TestLoop
FETCH NEXT FROM TestLoop INTO
@desc
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FullDesc = ISNULL(@FullDesc,'' +' '+ @desc
FETCH NEXT FROM TestLoop INTO
@desc
END
CLOSE TestLoop
DEALLOCATE TestLoop
SELECT @FullDesc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Late
Matt
------------
Jason Fitch at 8/22/2002 1:53:44 PM
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
-
? on how add strings together (reply)
This will work as well...
create proc testproc @tindex int
as
declare @x varchar(2000)
set @x = ''
select @x = @x + ' ' + description
from test t
where tindex = @tindex
select convert(varchar,@tindex) + ', ' + ltrim(rtrim(@x))
------------
Matthew at 8/22/2002 5:40:42 PM
cant seem to get my head around this one. but here is a cursor SP you can use with an input using the tindex. this isnt the most efficent way to do this, and i hate cursors, but here it is anyway. HTH
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
Name: GetFullDescription
Type: TSQL SP
Purpose: concat all related descriptions
Author: Matthew B. Nye
Created: 08.22.02
Modified:
Inputs/Outputs: Input related tindex
.:mbn|aug|mmii:.
*/
CREATE PROC GetFullDescription
@tindex int
AS
DECLARE
@desc varchar(25)
,@FullDesc varchar(1000)
DECLARE TestLoop CURSOR
FOR
SELECT description
FROM TEST
WHERE tindex = @tindex
OPEN TestLoop
FETCH NEXT FROM TestLoop INTO
@desc
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FullDesc = ISNULL(@FullDesc,'' +' '+ @desc
FETCH NEXT FROM TestLoop INTO
@desc
END
CLOSE TestLoop
DEALLOCATE TestLoop
SELECT @FullDesc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Late
Matt
------------
Jason Fitch at 8/22/2002 1:53:44 PM
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
-
? on how add strings together (reply)
Hey, I was going to say the same thing.
But, if you need to return more than one row, then the following is the only thing I can come up with. Kind of cheesy, but it works up to a certain number of rows per tindex
SELECT t1.tindex ,
ISNULL(t1.description, '' + ISNULL(t2.description, '' +ISNULL(t3.description, '' +ISNULL(t4.description, '' +ISNULL(t5.description, '' +ISNULL(t6.description, '' as description
FROM table t1
LEFT JOIN table t2 ON t1.tindex = t2.tindex AND (t2.tdline = t1.tdline + 1)
LEFT JOIN table t3 ON t1.tindex = t3.tindex AND (t3.tdline = t2.tdline + 1)
LEFT JOIN table t4 ON t1.tindex = t4.tindex AND (t4.tdline = t3.tdline + 1)
LEFT JOIN table t5 ON t1.tindex = t5.tindex AND (t5.tdline = t4.tdline + 1)
LEFT JOIN table t6 ON t1.tindex = t6.tindex AND (t6.tdline = t5.tdline + 1)
WHERE t1.tdline = 1
------------
Mark Neuenschwander at 8/22/2002 6:17:30 PM
This will work as well...
create proc testproc @tindex int
as
declare @x varchar(2000)
set @x = ''
select @x = @x + ' ' + description
from test t
where tindex = @tindex
select convert(varchar,@tindex) + ', ' + ltrim(rtrim(@x))
------------
Matthew at 8/22/2002 5:40:42 PM
cant seem to get my head around this one. but here is a cursor SP you can use with an input using the tindex. this isnt the most efficent way to do this, and i hate cursors, but here it is anyway. HTH
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
Name: GetFullDescription
Type: TSQL SP
Purpose: concat all related descriptions
Author: Matthew B. Nye
Created: 08.22.02
Modified:
Inputs/Outputs: Input related tindex
.:mbn|aug|mmii:.
*/
CREATE PROC GetFullDescription
@tindex int
AS
DECLARE
@desc varchar(25)
,@FullDesc varchar(1000)
DECLARE TestLoop CURSOR
FOR
SELECT description
FROM TEST
WHERE tindex = @tindex
OPEN TestLoop
FETCH NEXT FROM TestLoop INTO
@desc
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FullDesc = ISNULL(@FullDesc,'' +' '+ @desc
FETCH NEXT FROM TestLoop INTO
@desc
END
CLOSE TestLoop
DEALLOCATE TestLoop
SELECT @FullDesc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Late
Matt
------------
Jason Fitch at 8/22/2002 1:53:44 PM
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
-
? on how add strings together (reply)
Thanks all I'm going the SP route. I'm really trying to get VB.net to use this SP. Thanks again.
------------
Mark at 8/22/2002 6:23:23 PM
Hey, I was going to say the same thing.
But, if you need to return more than one row, then the following is the only thing I can come up with. Kind of cheesy, but it works up to a certain number of rows per tindex
SELECT t1.tindex ,
ISNULL(t1.description, '' + ISNULL(t2.description, '' +ISNULL(t3.description, '' +ISNULL(t4.description, '' +ISNULL(t5.description, '' +ISNULL(t6.description, '' as description
FROM table t1
LEFT JOIN table t2 ON t1.tindex = t2.tindex AND (t2.tdline = t1.tdline + 1)
LEFT JOIN table t3 ON t1.tindex = t3.tindex AND (t3.tdline = t2.tdline + 1)
LEFT JOIN table t4 ON t1.tindex = t4.tindex AND (t4.tdline = t3.tdline + 1)
LEFT JOIN table t5 ON t1.tindex = t5.tindex AND (t5.tdline = t4.tdline + 1)
LEFT JOIN table t6 ON t1.tindex = t6.tindex AND (t6.tdline = t5.tdline + 1)
WHERE t1.tdline = 1
------------
Mark Neuenschwander at 8/22/2002 6:17:30 PM
This will work as well...
create proc testproc @tindex int
as
declare @x varchar(2000)
set @x = ''
select @x = @x + ' ' + description
from test t
where tindex = @tindex
select convert(varchar,@tindex) + ', ' + ltrim(rtrim(@x))
------------
Matthew at 8/22/2002 5:40:42 PM
cant seem to get my head around this one. but here is a cursor SP you can use with an input using the tindex. this isnt the most efficent way to do this, and i hate cursors, but here it is anyway. HTH
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
Name: GetFullDescription
Type: TSQL SP
Purpose: concat all related descriptions
Author: Matthew B. Nye
Created: 08.22.02
Modified:
Inputs/Outputs: Input related tindex
.:mbn|aug|mmii:.
*/
CREATE PROC GetFullDescription
@tindex int
AS
DECLARE
@desc varchar(25)
,@FullDesc varchar(1000)
DECLARE TestLoop CURSOR
FOR
SELECT description
FROM TEST
WHERE tindex = @tindex
OPEN TestLoop
FETCH NEXT FROM TestLoop INTO
@desc
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FullDesc = ISNULL(@FullDesc,'' +' '+ @desc
FETCH NEXT FROM TestLoop INTO
@desc
END
CLOSE TestLoop
DEALLOCATE TestLoop
SELECT @FullDesc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Late
Matt
------------
Jason Fitch at 8/22/2002 1:53:44 PM
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|