Output a comma separated list in T-SQL
I have a table with phone numbers in it. Instead of spitting out a single row for each number I want to return a comma 开发者_JAVA百科separated list of phone numbers. What's the easiest way to do this in sql? A while loop?
Some of those answers are overly complicated with coalesce and more complex XML queries. I use this all the time:
select @Phones=(
Select PhoneColumn+','
From TableName
For XML Path(''))
-- Remove trailing comma if necessary
select @Phones=left(@Phones,len(@Phones)-1)
You could create a UDF that would do something like this
CREATE FUNCTION dbo.GetBirthdays(@UserId INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @combined VARCHAR(MAX)
SELECT @combined = COALESCE(@combined + ', ' + colName + ', colName)
FROM YourTable
WHERE UserId = @UserId
ORDER BY ColName
END
Basically this just pulls all of the values into a simple list.
FWIW I created a SQL CLR Aggregate function. Works like a champ!
[Serializable] [SqlUserDefinedAggregate(Format.UserDefined, Name = "JoinStrings", IsInvariantToNulls=true, IsInvariantToDuplicates=false, IsInvariantToOrder=false, MaxByteSize=8000)] public struct JoinStrings : IBinarySerialize { public string Result;
public void Init() { Result = ""; } public void Accumulate(SqlString value) { if (value.IsNull) return; Result += value.Value + ","; } public void Merge(JoinStrings Group) { Result += Group.Result; } public SqlString Terminate() { return new SqlString(Result.ToString().Trim(new
char[] { ',' })); }
public void Read(System.IO.BinaryReader r) { Result = r.ReadString(); } public void Write(System.IO.BinaryWriter w) { w.Write(Result.ToString()); } }
I can then use it like this:
SELECT dbo.JoinStrings(Phone) FROM Phones Where UserID = XXX
See my answer from this question. There are a couple of other ways to do it listed in that question also. COALESCE or for xml path should do the trick though.
Edit (added my answer from the previous question):
CREATE FUNCTION [dbo].[fn_MyFunction]()RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @str NVARCHAR(MAX)
DECLARE @Delimiter CHAR(2)
SET @Delimiter = ', '
SELECT @str = COALESCE(@str + @Delimiter,'') + AColumn
FROM dbo.myTable
RETURN RTRIM(LTRIM(@str))
END
Assuming you have a Customers table which has a unique ID and another table named PhoneNumbers with multiple phone numbers for each customer sharing the Customer ID field as a Foreign Key this would work using a correlated sub-Query
Select C.ID, C.FirstName, C.LastName,
(select (STUFF(( SELECT ', ' + PhoneNumber from PhoneNumbers P where P.CID = C.ID
FOR XML PATH('')), 1, 2, ''))) as PhoneNumbers
from Customers C
Select Unique ID, Replace(Rtrim(Ltrim(Case when [Phone_Number1] is not null Then [Phone_Number1]+' ' Else '' End +
Case when [Phone_Number2] is not null Then [Phone_Number2]+' ' Else '' End +
Case when [Phone_Number3] is not null Then [Phone_Number3]+' ' Else '' End)),' ',', ') as Phone_numbers
From MYTable
Hope this is what you are looking for and I dont know if this will help you so far after the question.
精彩评论