SQL Server 2008 R2多对多数据转单行交叉表实现需求问询

嘿，我来帮你搞定SQL Server 2008 R2里的交叉表转换需求！

从你给出的视图数据来看，目标应该是把Prop作为列名，每个列下显示对应Mainname该属性下的所有lineitems集合对吧？下面给你两种实用方案，按需选择：

方案一：静态交叉表（Prop取值固定时用） #

如果你的Prop值是固定的（比如只有P1、P2、P3、T4、Q1、Q2这些），直接写静态查询就行。因为SQL Server 2008 R2没有STRING_AGG函数，我们用FOR XML PATH来拼接字符串：

SELECT
    Mainname,
    -- 拼接P1对应的所有lineitems
    STUFF((SELECT ', ' + lineitems FROM YourView v2 WHERE v2.Mainname = v1.Mainname AND v2.Prop = 'P1' FOR XML PATH('')), 1, 2, '') AS P1,
    -- 拼接P2对应的所有lineitems
    STUFF((SELECT ', ' + lineitems FROM YourView v2 WHERE v2.Mainname = v1.Mainname AND v2.Prop = 'P2' FOR XML PATH('')), 1, 2, '') AS P2,
    -- 拼接P3对应的所有lineitems
    STUFF((SELECT ', ' + lineitems FROM YourView v2 WHERE v2.Mainname = v1.Mainname AND v2.Prop = 'P3' FOR XML PATH('')), 1, 2, '') AS P3,
    -- 拼接T4对应的所有lineitems
    STUFF((SELECT ', ' + lineitems FROM YourView v2 WHERE v2.Mainname = v1.Mainname AND v2.Prop = 'T4' FOR XML PATH('')), 1, 2, '') AS T4,
    -- 拼接Q1对应的所有lineitems
    STUFF((SELECT ', ' + lineitems FROM YourView v2 WHERE v2.Mainname = v1.Mainname AND v2.Prop = 'Q1' FOR XML PATH('')), 1, 2, '') AS Q1,
    -- 拼接Q2对应的所有lineitems
    STUFF((SELECT ', ' + lineitems FROM YourView v2 WHERE v2.Mainname = v1.Mainname AND v2.Prop = 'Q2' FOR XML PATH('')), 1, 2, '') AS Q2
FROM YourView v1
GROUP BY Mainname
ORDER BY Mainname;

执行后会得到这样的结果：

方案二：动态交叉表（Prop可能新增时用） #

如果以后会有新的Prop值加入，静态查询就得每次修改，这时候用动态SQL更省心，它会自动识别所有Prop并生成对应列：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 自动获取所有不重复的Prop，拼接成列名（用QUOTENAME避免特殊字符问题）
SET @cols = STUFF((SELECT DISTINCT ', ' + QUOTENAME(Prop) 
                   FROM YourView
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 构建动态查询语句
SET @query = '
SELECT Mainname, ' + @cols + '
FROM (
    SELECT 
        Mainname,
        Prop,
        -- 先拼接每个Mainname+Prop对应的lineitems
        STUFF((SELECT '', '' + lineitems FROM YourView v2 WHERE v2.Mainname = v1.Mainname AND v2.Prop = v1.Prop FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''') AS lineitems
    FROM YourView v1
    GROUP BY Mainname, Prop
) AS src
-- 用PIVOT转成交叉表
PIVOT (
    MAX(lineitems)
    FOR Prop IN (' + @cols + ')
) AS pvt
ORDER BY Mainname;';

-- 执行动态查询
EXEC sp_executesql @query;

小提示 #

• 记得把代码里的YourView替换成你实际的视图名称！
• 如果lineitems里有&、<、>这类特殊字符，静态查询里也可以改成动态方案里的FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')写法，避免字符被转义。

内容的提问来源于stack exchange，提问作者Joe Wells