sql server 行转列

将sql server查出的多行结果，变为一行多列显示
用到PIVOT聚合函数，用于将列值旋转为列名（即行转列）

PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

完整语法：

table_source

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN()

)

一、采用变量方式
将要转成行的列放在colList 参数中

DECLARE @sql NVARCHAR(MAX), @colList NVARCHAR(MAX)SET @colList = STUFF((SELECT ','+QUOTENAME(pp.attrName) FROM         (            SELECT pt.id,pt.name  Name ,cc.name categoryName,bb.name brandName, pa.name attrName, pav.value attrValue             FROM  temp pt            LEFT JOIN   attrValue  pav ON pav.id = pt.id            LEFT JOIN  attr pa ON pa.id = pav.attr_id            LEFT JOIN category cc ON cc.id = pt.category_id            LEFT JOIN brand bb ON bb.id = pt.brand_id        ) pp         GROUP BY pp.attrName ORDER BY pp.attrName FOR XML PATH('')),1,1,'')SET @sql = N'    select * from         (            SELECT pt.id,pt.name  Name ,cc.name categoryName,bb.name brandName, pa.name attrName, pav.value attrValue             FROM  temp pt            LEFT JOIN   attrValue  pav ON pav. id = pt.id            LEFT JOIN  attr pa ON pa.id = pav.attr_id            LEFT JOIN category cc ON cc.id = pt.category_id            LEFT JOIN brand bb ON bb.id = pt.brand_id        ) pp     pivot    (max(pp.attrValue) for pp.attrName in('+ @colList +')) b'EXEC(@sql)

二、不使用参数，

select * from (        SELECT pt.id,pt.name  Name,brand.name brandName, pa.name attrName, pav.value attrValue         FROM (            select pt.* from  temp pt,(            select pt1.name from  temp pt1,  p where pt1.name=p.name group by pt1.name having count(pt1.name)>1) a            where pt.name=a.name            ) pt        LEFT JOIN   attrValue  pav ON pav. _id = pt.id        LEFT JOIN  attr pa ON pa.id = pav.attr_id        LEFT JOIN brand on brand.id=pt.brand_id    ) pp pivot(max(pp.attrValue) for pp.attrName in(xxx1,[xxx2],[xxx3],[xxx4])) b