November 12, 2008 at 12:23 pm
Hello all,
It's been some time since I've written a recursive CTE and I'm having a difficult time.
Given this test code can you help me generate a result set?
DECLARE @test-2 TABLE
(ID INT, TXT VARCHAR (MAX))
INSERT INTO @test-2
SELECT 1,'A'
UNION
SELECT 2,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,'A'
UNION
SELECT 3,'B'
UNION
SELECT 3,'C'
For each ID, I need to "flatten" the TXT column into a concatenated field. So the result set I am looking for is this:
ID TXT
1 A
2 A,B
3 A,B,C
For each given ID there is any number of TXT fields, so the CTE needs to recurse for each ID for count (Txt) number of times. I am this far already, but as you can see from my result set, my logic is not correct somewhere:
;WITH CTE (ID,TXT) AS
(SELECT 0,CAST (''AS VARCHAR(MAX))
UNION ALL
SELECT T.ID , CAST ((CTE.TXT + ',' + T.TXT)AS VARCHAR(MAX))
FROM @test-2 T
JOIN CTE ON CTE.ID < T.ID)
SELECT ID,SUBSTRING (TXT ,2,LEN(TXT)) FROM CTE
ORDER BY ID
Any assistance would be greatly appreciated. Thank you.
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
November 12, 2008 at 1:16 pm
Basically, looking for a CTE solution to this CURSOR MESS:
DECLARE @test-2 TABLE(ID INT, TXT VARCHAR (MAX))
INSERT INTO @test-2
SELECT 1,'A'
UNION
SELECT 2,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,'A'
UNION
SELECT 3,'B'
UNION
SELECT 3,'C'
DECLARE @RESULT TABLE(ID INT, TXT VARCHAR (MAX))
DECLARE @ID INT
DECLARE @TXT VARCHAR (MAX)
DECLARE @TXT2 VARCHAR (MAX)
DECLARE CURID CURSOR FOR
SELECT DISTINCT ID FROM @test-2 --LOOP THROUGH IDS
OPEN CURID
FETCH NEXT FROM CURID INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TXT2 = NULL
DECLARE CURTXT CURSOR FOR
SELECT TXT FROM @test-2 WHERE ID = @ID --LOOP THROUGH TEXT FOR EACH ID
OPEN CURTXT
FETCH NEXT FROM CURTXT INTO @TXT
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TXT2 = ISNULL (@TXT2,'') + ',' + @TXT
FETCH NEXT FROM CURTXT INTO @TXT
END
CLOSE CURTXT
DEALLOCATE CURTXT
INSERT INTO @RESULT
SELECT @ID, SUBSTRING (@TXT2,2,LEN(@TXT2)-1)
FETCH NEXT FROM CURID INTO @ID
END
CLOSE CURID
DEALLOCATE CURID
SELECT * FROM @RESULT
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
November 12, 2008 at 1:30 pm
There may be a better way, but I hope this helps:
DECLARE @test-2 TABLE
(ID INT, TXT VARCHAR (MAX))
INSERT INTO @test-2
SELECT 1,'A'
UNION
SELECT 2,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,'A'
UNION
SELECT 3,'B'
UNION
SELECT 3,'C'
declare @txt varchar(max);
select
b.ID,
(select
isnull(@txt + ', ', '') + a.TXT
from
@test-2 a
where
a.ID = b.ID
for xml path (''))
from
@test-2 b
November 12, 2008 at 2:21 pm
Thank you Lynn,
My XML brain is going to have to grind on that for awhile!
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
November 12, 2008 at 3:19 pm
Here is a slightly different version doing the same thing.
DECLARE @test-2 TABLE
(ID INT, TXT VARCHAR (MAX))
INSERT INTO @test-2
SELECT 1,'A'
UNION
SELECT 2,'A'
UNION
SELECT 2,'B'
UNION
SELECT 3,'A'
UNION
SELECT 3,'B'
UNION
SELECT 3,'C'
declare @txt varchar(max);
select
b.ID,
stuff((select
', ' + a.TXT
from
@test-2 a
where
a.ID = b.ID
for xml path ('')), 1,2,'')
from
@test-2 b
November 13, 2008 at 8:33 am
I was originally thinking of using FOR XML PATH as well, but the TEXT references and the varchar(max) made me worry about getting the commas in there properly.
Todd, you are dealing with very long strings(of variable length), likely containing spaces in your actual data, rather than the simple 'A','B','C' data supplied here for testing? If so, I think this solution will require an additional piece.
November 14, 2008 at 9:21 am
Yes, the sample is just to get the code running. In reality, multiple variable-length fields containing any number of characters as well, including spaces... so I'm not sure this will work if a space character is in the field.
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
November 14, 2008 at 9:30 am
Can your data include commas as well? Let's have a for instance:
ID TXT
1 This is my first line of data, isn't it awesome?
1 Ooooh here's some more data, also going to line 1, also awesome.
1 Line 1, Line 1, he's the man... and he likes random punctuation "';][\()*&^%$#@!
How would you like these 3 text strings to look when flattened?
November 14, 2008 at 10:25 am
This code handles the values provided by Seth.
DECLARE @test-2 TABLE
(ID INT, TXT VARCHAR (MAX))
INSERT INTO @test-2
SELECT 1,'A'
UNION ALL
SELECT 2,'A'
UNION ALL
SELECT 2,'B'
UNION ALL
SELECT 3,'A'
UNION ALL
SELECT 3,'B'
UNION ALL
SELECT 3,'C'
UNION ALL
SELECT 4,'This is my first line of data, isn''t it awesome?'
UNION ALL
SELECT 4,'Ooooh here''s some more data, also going to line 1, also awesome.'
UNION ALL
SELECT 4,'Line 1, Line 1, he''s the man... and he likes random punctuation "'';][\()*&^%$#@!'
select * from @test-2
declare @txt varchar(max);
select
b.ID,
stuff((select
', ' + a.TXT
from
@test-2 a
where
a.ID = b.ID
for xml path ('')), 1,2,'')
from
@test-2 b
November 14, 2008 at 10:41 am
Lynn Pettis (11/14/2008)
This code handles the values provided by Seth.
Oops, yeah it does. Ignore my earlier comments about the extra piece, I keep misreading STUFF.:blink:
As a side note, the result of that is:
This is my first line of data, isn't it awesome?, Ooooh here's some more data, also going to line 1, also awesome., Line 1, Line 1, he's the man... and he likes random punctuation "';][\()*&a.m.p.;.^%$#@!
I wonder where & gets converted to &.a.m.p.; . I'm guessing it's from the XML conversion.
[EDIT] Wow, this post doesn't make much sense when the forums own my &.a.m.p.;. 's. Had to add the period's so it didn't convert them :/.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply