sql纵表转横表

纵表product_attribute

product_id    attribute_name  attribute_value

1                   book_name          数据结构

1                    price                    20.1

1                   publication_date  2011-01-04

2                   book_name          C语言程序设计

2                   price                     30.1

2                   publication_date  2011-02-16

 

横表

book_id       book_name         price   publication_date

1                    数据结构              20.1    2011-01-04

2                     C语言程序设计     30.1    2011-02-16

 

sql:

select productid as book_id,

    max(case attribute_name
        when 'book_name' then
            value
        else
            0
        end) as book_name,
   max(case attribute_name
        when 'price' then
            value
        else
            0
        end) as price,
   max(case attribute_name
        when 'publication_date' then
            value
        else
            0
        end) as publication_date
    from product_attribute group by book_id;

 

如果是数字：max可以用sum代替

---