x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Build a Comma Delemited String For All Rows

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.

http://www.sqlteam.com/item.asp?ItemID=2368

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:

http://www.sqlteam.com/forums/members.asp

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

http://www.dbforums.com/t1084977.html

OK, I got this from MWolf at 

http://www.dbforums.com/member.php?userid=99822

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)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)

SELECT @Result = COALESCE(@Result + ', ','') + col2
FROM myTable99
WHERE col1=@id

RETURN @Result
END
GO

SELECT Col1, dbo.GetAllOnLine(Col1) FROM MyTable99

 

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 char(1))
GO

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'
GO

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
  BEGIN
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
  END

SELECT * FROM @rs
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

 

Print | posted on Wednesday, January 05, 2005 2:22 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Build a Comma Delemited String For All Rows

Did some research on this, posted here:

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Also some nice debate in the comments if you care to read that far :)
1/7/2005 4:24 PM | Adam Machanic
Gravatar

# .text Error

Adam,

I get an error when opening that link. Any ideas why?

Thanks fo r the post though.

1/10/2005 8:28 AM | Brett
Gravatar

# re: Build a Comma Delemited String For All Rows

Yes, the sqljunkies server had an error this morning. It's been fixed now... Give it another try if you get a chance.
1/10/2005 4:15 PM | Adam Machanic
Gravatar

# New twist on Delimited string per ID using many columns...AND this time into a table

2/23/2005 3:30 PM | x002548's Blog
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET