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