考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
考虑一个包含名称的数据库表,其中有三行:
Peter
Paul
Mary
有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?
当前回答
SELECT PageContent = Stuff(
( SELECT PageContent
FROM dbo.InfoGuide
WHERE CategoryId = @CategoryId
AND SubCategoryId = @SubCategoryId
for xml path(''), type
).value('.[1]','nvarchar(max)'),
1, 1, '')
FROM dbo.InfoGuide info
其他回答
我在家里无法访问SQL Server,所以我猜测这里的语法,但大致上是这样的:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
Oracle有两种方法:
create table name
(first_name varchar2(30));
insert into name values ('Peter');
insert into name values ('Paul');
insert into name values ('Mary');
解决方案是1:
select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
o/p=> Peter,Paul,Mary
解决方案是2:
select rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
o/p=> Peter,Paul,Mary
我真的很喜欢Dana回答的优雅,只想让它变得完整。
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
以下是实现这一目标的完整解决方案:
-- 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)
使用“TABLE”类型非常容易。让我们假设您的表名为Students,并且它具有列名。
declare @rowsCount INT
declare @i INT = 1
declare @names varchar(max) = ''
DECLARE @MyTable TABLE
(
Id int identity,
Name varchar(500)
)
insert into @MyTable select name from Students
set @rowsCount = (select COUNT(Id) from @MyTable)
while @i < @rowsCount
begin
set @names = @names + ', ' + (select name from @MyTable where Id = @i)
set @i = @i + 1
end
select @names
此示例使用SQL Server 2008 R2进行了测试。