考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
我通常使用这样的select连接SQL Server中的字符串:
with lines as
(
select
row_number() over(order by id) id, -- id is a line id
line -- line of text.
from
source -- line source
),
result_lines as
(
select
id,
cast(line as nvarchar(max)) line
from
lines
where
id = 1
union all
select
l.id,
cast(r.line + N', ' + l.line as nvarchar(max))
from
lines l
inner join
result_lines r
on
l.id = r.id + 1
)
select top 1
line
from
result_lines
order by
id desc
其他回答
使用此项:
ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'
其中“300”可以是任何宽度,考虑到您认为将显示的最大项目数。
此答案可能会返回意外的结果。要获得一致的结果,请使用其他答案中详细说明的For XML PATH方法之一。
使用COALENCE:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
只是一些解释(因为这个答案似乎得到了相对规律的观点):
联合实际上只是一种有助于实现两件事的欺骗:
1) 无需使用空字符串值初始化@Names。
2) 无需在末端去除额外的分隔符。
如果一行具有NULL Name值(如果存在NULL,NULL将使该行之后的@Names为NULL,而下一行将再次以空字符串开始),则上述解决方案将给出错误的结果。使用以下两种解决方案之一即可轻松解决:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
or:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
取决于您想要的行为(第一个选项只是过滤掉NULL,第二个选项用标记消息将它们保留在列表中[用适合您的内容替换“N/a”])。
这也很有用
create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')
DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test
回报
Peter,Paul,Mary
declare @phone varchar(max)=''
select @phone=@phone + mobileno +',' from members
select @phone
以下是实现这一目标的完整解决方案:
-- Table Creation
CREATE TABLE Tbl
( CustomerCode VARCHAR(50)
, CustomerName VARCHAR(50)
, Type VARCHAR(50)
,Items VARCHAR(50)
)
insert into Tbl
SELECT 'C0001','Thomas','BREAKFAST','Milk'
union SELECT 'C0001','Thomas','BREAKFAST','Bread'
union SELECT 'C0001','Thomas','BREAKFAST','Egg'
union SELECT 'C0001','Thomas','LUNCH','Rice'
union SELECT 'C0001','Thomas','LUNCH','Fish Curry'
union SELECT 'C0001','Thomas','LUNCH','Lessy'
union SELECT 'C0002','JOSEPH','BREAKFAST','Bread'
union SELECT 'C0002','JOSEPH','BREAKFAST','Jam'
union SELECT 'C0002','JOSEPH','BREAKFAST','Tea'
union SELECT 'C0002','JOSEPH','Supper','Tea'
union SELECT 'C0002','JOSEPH','Brunch','Roti'
-- function creation
GO
CREATE FUNCTION [dbo].[fn_GetItemsByType]
(
@CustomerCode VARCHAR(50)
,@Type VARCHAR(50)
)
RETURNS @ItemType TABLE ( Items VARCHAR(5000) )
AS
BEGIN
INSERT INTO @ItemType(Items)
SELECT STUFF((SELECT distinct ',' + [Items]
FROM Tbl
WHERE CustomerCode = @CustomerCode
AND Type=@Type
FOR XML PATH(''))
,1,1,'') as Items
RETURN
END
GO
-- fianl Query
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type)
from Tbl
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT CustomerCode,CustomerName,' + @cols + '
from
(
select
distinct CustomerCode
,CustomerName
,Type
,F.Items
FROM Tbl T
CROSS APPLY [fn_GetItemsByType] (T.CustomerCode,T.Type) F
) x
pivot
(
max(Items)
for Type in (' + @cols + ')
) p '
execute(@query)