sqlserver列转行（多次分组）

select     CASE r.rule_category
        WHEN '1' THEN
        '温度'
        WHEN '2' THEN '湿度'
        WHEN '3' THEN '气压'
        ELSE '未知' END+r.name as name,r.ruleIds
        from (
select rule_category,level,name,stuff((select ','+convert(varchar,rule_id) 
        from alarm_rule where rule_category = e.rule_category and level=e.level
         for xml path('')),1,1,'') as 
        ruleids from alarm_rule e
group by rule_category,level,name
) r