3 回答
TA贡献1859条经验 获得超6个赞
看看这个
DECLARE @Reviews TABLE(
ReviewID INT,
ReviewDate DATETIME
)
DECLARE @Reviewers TABLE(
ReviewerID INT,
ReviewID INT,
UserID INT
)
DECLARE @Users TABLE(
UserID INT,
FName VARCHAR(50),
LName VARCHAR(50)
)
INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'
INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''
INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5
SELECT *,
(
SELECT u.FName + ','
FROM @Users u INNER JOIN
@Reviewers rs ON u.UserID = rs.UserID
WHERE rs.ReviewID = r.ReviewID
FOR XML PATH('')
) AS Products
FROM @Reviews r
TA贡献1871条经验 获得超13个赞
事实证明,有一种更简单的方法可以做到这一点,不需要UDF:
select replace(replace(replace((cast(( select distinct columnName as X from tableName for xml path('')) as varchar(max))), '</X><X>', ', '),'<X>', ''),'</X>','')
TA贡献1797条经验 获得超6个赞
有类似的问题,并在玩代码15分钟后找到了一个甜蜜的解决方案
declare @result varchar(1000)select @result = COALESCE(@result+','+A.col1, A.col1) FROM ( select col1 from [table] ) Aselect @result
返回结果为value1,value2,value3,value4
请享用 ;)
- 3 回答
- 0 关注
- 414 浏览
添加回答
举报