August 25, 2004 at 9:14 am
My issue is this. I have a table that consists of item (int), seqn (smallint) and desx char (50) that has all the info I need. The item column is the main reference field but is not a unique. The seqn column is unique but only to that corresponding item. The desx is the info I am trying to pull and piece together.
eg.
item seqn desx
12345 1 this
12345 3 is
12345 7 a
12345 10 test
54892 1 this
54892 3 is
54892 7 also a
54892 10 test
What I would like to do is pull everything out under the desx column based on all that are of the same item in order by seqn (the seqn is not always the same numbers but the next line of text is always a higher number). I have found out how to it using cursors and fetch individually but I am trying to find a way to do it as a mass with something like a select with a where thistable.item = thattable.item. My end result that I am looking for would be new table(using the sample table from above):
New Table
item seqn (no need for it in new table) desx
12345--------------------------------------This is a test
54892--------------------------------------This is also a test
Any help would be greatly appreciated. Thanks.
Ken
August 25, 2004 at 10:42 am
This should do the trick. It uses looping but avoids using cursors
SET NOCOUNT ON
CREATE TABLE New (
item INT NOT NULL,
desx VARCHAR(100) NOT NULL)
DECLARE @items TABLE(
nid INT IDENTITY(1,1),
item INT)
INSERT INTO @items
SELECT DISTINCT item
FROM TestCat -- (This is the table I used to store your values)
DECLARE @text VARCHAR(2000)
DECLARE @current_pos INT
DECLARE @end INT
DECLARE @current_item INT
SET @current_pos = 1
SET @end = (SELECT MAX(nid) FROM @items)
WHILE @current_pos <= @end
BEGIN
SET @text = ''
SET @current_item = (SELECT item FROM @items WHERE nid = @current_pos)
SELECT @text = @text + RTRIM(LTRIM(desx)) + ' '
FROM TestCat
WHERE item = @current_item
ORDER BY seqn
INSERT INTO New
SELECT
@current_item,
@text
SET @current_pos = @current_pos + 1
END
SELECT *
FROM New
item desx
----------- ------------------------------------------------------------
12345 this is a test
54892 this is also a test
August 25, 2004 at 1:36 pm
Thanks alot! It does exactly what I needed but it is(of course) limited to 256 characters. The rows I am merging could up to a total of 1610. Ideally I would like it to do infinate but 1610 would get me past this hurtle. Is there any way to correct the 256 limitation?
August 25, 2004 at 1:36 pm
Thanks alot! It does exactly what I needed but it is(of course) limited to 256 characters. The rows I am merging could up to a total of 1610. Ideally I would like it to do infinate but 1610 would get me past this hurtle. Is there any way to correct the 256 limitation?
August 25, 2004 at 1:53 pm
If you need 1610 char's then:
CREATE TABLE New (
item INT NOT NULL,
desx VARCHAR(1610 - 8000) NOT NULL)
if 8000 char's is not big enough then make "desx" a text file which can hold up to 2 gig.
DECLARE @text VARCHAR(1610 - 8000)
August 25, 2004 at 2:06 pm
It just dawned on me where you are running into the 256 char limit. In you query analyzer select from the top bar menu "Tools" then "Options". Select the "Results" tab and change the "Maximum characters per column:" to 2000 or 8000 and "Apply".
Then rerun your query
August 26, 2004 at 3:22 am
Interesting coincidence... this is already third question of the same type today. Please see my reply in this thread : http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=133911
You'll have to change the length of returned string to 1610 (or whatever - but both in RETURNS and DECLARE) and add the correct ordering to ORDER BY seqn... Otherwise just replace the table and variable names with what you need, delimiter with space, and it should work. Oh, and you don't need the final trimming of trailing delimiter, since the trailing space will be removed automatically (it's a varchar).
HTH, Vladan
August 26, 2004 at 6:37 am
Thanks all. The option was the thing that fixed it.
August 26, 2004 at 8:19 am
Another solution that I found on this forum:
ALTER FUNCTION udf_concat (@i INT) RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @concat VARCHAR(100)
SELECT @concat = ISNULL (@concat, '') + col +' '
FROM test WHERE col1 = @i
ORDER BY col2
RETURN @concat
END
GO
SELECT col1, dbo.udf_concat (col1)
FROM test
GROUP BY col1
col1
----------- ----------------------
12345 this is a test
54892 this is also a test
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply