【Amadeus原创】SQLServer使用STUFF-for xml path实现结果行列转置

2年前 (2022) 程序员胖胖胖虎阿

171 0 0

源数据：

场景1： 查出用户的爱好，并进行行列转置

select cname,
stuff((select ','+f.favor from tb_favor f where f.userid=b.userid FOR XML Path('')),1,1,'') favor
from tb_user b

这里使用了SQL Server 2005版本以后加入的stuff以及for xml path:

FOR XML Path('') 这句是把得到的内容以XML的形式显示。

stuff((select ','+f.favor from tb_favor f where f.userid=b.userid FOR XML Path('')),1,1,'') 这句是把拼接的内容的第一个“,”去掉。

看一下结果：

场景2：将情景1的favor结果，变成中文。这需要先拆了，再拼接一次。

demo表：这次需要将favor通过tb_favorCname表，替换成中文

sql语句：

select cname 姓名,
stuff((select '、'+fc.cname from tb_favorCname fc,dbo.FnSplitStr((select favor from tb_demo1 d where d.username=bb.username ), ',') tt where fc.favor=tt.F1 for xml path('')),1,1,'')  兴趣
from tb_user bb

结果：

将逗号拆掉的函数：

ALTER FUNCTION [dbo].[FnSplitStr]
(
    @str nvarchar(max),
    @split nchar(1)
)
RETURNS @temp TABLE ( F1 nvarchar(500) )
AS
    BEGIN   
        DECLARE @ch AS nvarchar(500);   
        DECLARE @i_p as int;
        DECLARE @i_c as int;
        DECLARE @len as int;
        set @i_p = 0;
        set @i_c = CHARINDEX(@split, @str);
        set @len = len(@str);
        if @len > 0
        begin
        while @i_c <> 0 
        begin
            set @ch = substring(@str, @i_p + 1, @i_c - @i_p - 1);
            INSERT @temp VALUES (@ch);
            set @i_p = @i_c;
            set @i_c = CHARINDEX(@split, @str, @i_c + 1);
        end
        set @ch = substring(@str, @i_p + 1, @len - @i_p);
        INSERT @temp VALUES (@ch);
        end

        RETURN   
    END