的SQL Server 2014插入的东西功能
问题描述:
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
嗨,我使用SQL Server 2014,我想知道这是否是可以使用的东西()函数来插入结果成一列。我在网上看到的例子都在检索。我试图根据它的文档做它似乎并不正确。的SQL Server 2014插入的东西功能
查询
@"INSERT INTO ApplicationOtherInfo
(ApplicationId, AppOptionCode
) values
(@applicationId, @appCode
)";
SELECT STUFF((SELECT ',' + AppOptionCode
FROM ApplicationOtherInfo
ORDER BY AppOptionCode
FOR XML PATH('')), 1, 1, '')
答
是,您可以:
DECLARE @Students TABLE (
SubjectID int,
StudentName nvarchar(max)
)
INSERT INTO @Students VALUES
(1, 'Mary'),
(1, 'John'),
(1, 'Sam')
INSERT INTO SomeTable
SELECT DISTINCT
s.SubjectID,
STUFF((SELECT ', ' + StudentName
FROM @Students
WHERE s.SubjectID = SubjectID
FOR XML PATH('')), 1, 2, '')
as StudentName
FROM @Students s
如果从SomeTable
选择您将获得:
SubjectID StudentName
1 Mary, John, Sam
+0
你有没有尝试过我的方案? – gofr1
是的,它是:https://开头MSDN。 microsoft.com/en-us/library/ms188043.aspx – rbr94