sql数据行转列-优快云博客

本文介绍了一种复杂的SQL查询技巧，通过使用Pivot函数来聚合和展示多个类别的费用数据，如保洁费、材料费等。该方法首先从CodeDictionary表中选取特定类别ID的数据，然后构造动态SQL语句，最终实现对Housebill和Chargebill表的复杂联接查询，以Pivot形式展示结果。

select CodeName FROM CodeDictionary where CodeCategory_ID=138
结果：

开始转换：
DECLARE @sql VARCHAR(8000)
    SELECT @sql=isnull(@sql+',','')+CodeName FROM CodeDictionary where CodeCategory_ID=138 GROUP BY CodeName           
    SET @sql='select * from (select ch.HousebillID,ch.Chargeamount,cd.CodeCategory_ID,cd.CodeName 
        from Housebill hb left join Chargebill ch on hb.ID=ch.HousebillID left join CodeDictionary cd 
        on ch.Chargeitem=cd.ID where cd.CodeCategory_ID=138) tb pivot (max(tb.Chargeamount) for tb.CodeName in ('+@sql+'))a'
   exec(@sql)

结果就类似这样的语句：
select * from (
        select ch.HousebillID,ch.Chargeamount,cd.CodeCategory_ID,cd.CodeName 
        from Housebill hb left join Chargebill ch on hb.ID=ch.HousebillID left join CodeDictionary cd on ch.Chargeitem=cd.ID where cd.CodeCategory_ID=138
    ) 
    tb pivot (sum(tb.Chargeamount) 
    for tb.CodeName in (保洁费,材料费,电费,服务费,宽带,其他,燃气费,水费,维修费,卫生,物业费,押金,优惠,有线电视,租金)
)a
where CodeCategory_ID=138