行变列 拼接字符串 MSSQL 一个超级搞的问题

由数据
ThreeLevelSortID KeyWordID   KeyWordName
2             142         key1
2             142         key2
3             143         key3
3             144         key4
得到数据大
ThreeLevelSortID KeyWordName
2                key1,key2
3                key3,key4
declare @tblTmp table (id int,ThreeLevelSortID int,KeyWordName nvarchar(max))
declare @tblTmpCount int
insert @tblTmp(id,ThreeLevelSortID)
select ROW_NUMBER() over (order by t.ThreeLevelSortID), t.ThreeLevelSortID 
from ( select Distinct ThreeLevelSortID from tbl_ThreeLevelSortKeyWordsDetail) t
select @tblTmpCount = count(1) from @tblTmp
declare @index int
set @index = 0
While(@index<)
begin
declare @currentThreeLevelSortID int
select @currentThreeLevelSortID = ThreeLevelSortID
from @tblTmp where id = @index
--拼接关键词字符串
declare @keywords nvarchar(max)
select @keywords=ISNULL(@keywords+;,‘‘)+k.KeyWordName from 
(SELECT h.KeyWordID,h.ThreeLevelSortID,j.KeyWordName FROM dbo.tbl_ThreeLevelSortKeyWordsDetail h,
dbo.tbl_KeyWords j
WHERE ThreeLevelSortID = @currentThreeLevelSortID
AND h.KeyWordID = j.KeyWordID) k
update @tblTmp set KeyWordName = @keywords 
where ThreeLevelSortID = @currentThreeLevelSortID
set @keywords = null
set @index = @index +1
end
select * from @tblTmp
求简单方法

 

行变列 拼接字符串 MSSQL 一个超级搞的问题

上一篇:mysql知识汇总


下一篇:10.Struts2连接数据库