Seems to be a popular question people seem to look for. I Still don't know why. Kinda hard to perform set processing against data put in this form. I forget the first day I saw this, or from whom, but I always reference this article on the great SQLTeam Site.
So, once they got the ability to do it for 1 ID, they'd like the Whole thing denormalized...so I'll post mine here so I don't have to retpe (lord knows I could use the practice though). It's been done over and over, and I'm sure the original came from someone here (especially the part about loosing the Cursor). I've just received so much knowledge from the “Team“ that's hard to keep track (I still have to post Arnolds time conversion that he was so gracious to allow me to post). Thanks again to all the members:
So I take no credit for any of this, I'm just being lazy.
The original thread that got me to post this, because I was tired of retyping it from scratch
OK, I got this from MWolf at
It seems that the plan for this much moer effecient. I'll need to investigate....see for your self...use with the code below..
CREATE FUNCTION GetAllOnLine(@id int)
DECLARE @Result VARCHAR(8000)
SELECT @Result = COALESCE(@Result + ', ','') + col2
SELECT Col1, dbo.GetAllOnLine(Col1) FROM MyTable99
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 char(1))
INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 1, 'D' UNION ALL
SELECT 1, 'P' UNION ALL
SELECT 2, 'F' UNION ALL
SELECT 2, 'W' UNION ALL
SELECT 3, 'X' UNION ALL
SELECT 3, 'Y' UNION ALL
SELECT 4, 'Z' UNION ALL
SELECT 5, 'O'
DECLARE @MAX_Col1 int, @Col1 int, @strCol2 varchar(8000)
DECLARE @rs table(Col1 int, strCol2 varchar(8000))
SELECT @MAX_Col1 = MAX(Col1), @Col1 = MIN(Col1) FROM myTable99
WHILE @MAX_Col1 > = @Col1
SELECT @strCol2 = COALESCE(@strCol2 + ', ','') + Col2 FROM myTable99 WHERE Col1 = @Col1
INSERT INTO @rs(Col1, strCol2) SELECT @Col1, @strCol2
SELECT @Col1 = MIN(Col1), @strCol2 = null FROM myTable99 WHERE Col1 > @Col1
SELECT * FROM @rs
SET NOCOUNT OFF
DROP TABLE myTable99