Printable View
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.
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.
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.
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.
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.