You are currently viewing our boards as a guest which gives you limited access to view most discussions, articles and access our other FREE features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload your own photos and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!
If you have any problems with the registration process or your account login, please contact contact support.
If you would prefer not to see any double-underlined words and corresponding advertisements, please click here.
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
To Brett Kaiser
Why so difficult?
You can create function:
CREATE FUNCTION GetAllOnLine(@id int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)
SET @Result = ''
SELECT @Result = @Result + col2 + ' ,'
FROM YourTable
WHERE col1=@id
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
But don't you double the access paths to the table? One for the UDF and another for the SELECT? If it's a lot of rows I think mine would be more effecient...(though I still would like to know why anyone would like to do this)
Also, I don't usually like to make my UDF's some very specific, I prefer them to be as generic as possible. You could only use this for one very specific case.
Do you mind if I blog your solution as another example?
This is execution plan of my result query:
|--Compute Scalar(DEFINE[Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
|--Hash Match(Aggregate, HASH[Mail].[CC]), RESIDUAL[Mail].[CC]=[Mail].[CC]))
|--Clustered Index Scan(OBJECT[MWolf_Mail].[dbo].[Mail].[PK_Mail]))
It is better plan that I saw. Unfortunately, specific UDF is pay for good performance.
With code tags perhaps...very nice plan..do you mind if I blog this with the appropriate credit?
Code:
This is execution plan of my result query:
|--Compute Scalar(DEFINE:([Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
|--Hash Match(Aggregate, HASH:([Mail].[CC]), RESIDUAL:([Mail].[CC]=[Mail].[CC]))
|--Clustered Index Scan(OBJECT:([MWolf_Mail].[dbo].[Mail].[PK_Mail]))
It is better plan that I saw. Unfortunately, specific UDF is pay for good performance.
If i understood you correctly, you want to post my example somewhere for others to see it and learn (please apologize my bad English)
If i'm right i have no objections else please correct me
[Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
Linear Mode
